Dealing with large amounts of SQL data

[optik]

Hi

I'm just wondering about the best strategies when dealing with tables that have millions of entries.

Can php/sql cope easily enough with a single table of millions of entries or is best to create a series of tables and what are the best ways to go around this problem?

Is there a fixed number of rows possible, and at what point would searching in such a large table for a single row or numbers of rows become problematic if at all?

Thanks

[andrewsmd]

To search the table you will need to have full text indexing enabled to be efficient. To access the data stored procedures will help immensely. What database architecture are you using? Mysql, sql server, oracle? For displaying the rows you will want to limit the data to something like 50 a page and set up a paging system because loading a million records for every user would not work.

[optik]

The data will not be presented to the user but will act as a central index for other tables in the database, what do you mean by stored procedures?

I'll be using mySql

[andrewsmd]

What version of MySQL

[andrewsmd]

[dev.mysql.com...]
[dev.mysql.com...]

[optik]

I'm using mySql 5, stored procedures are new to me, looks like I have some homework to do.

[brotherhood of LAN]

Partitioning is also worth reading

[dev.mysql.com...]

Partitioning takes this notion a step further, by allowing you to distribute portions of individual tables across a file system according to rules which you can set largely as needed. In effect, different portions of a table are stored as separate tables in different locations.

Some queries can be greatly optimized in virtue of the fact that data satisfying a given WHERE clause can be stored only on one or more partitions, thereby excluding any remaining partitions from the search. Because partitions can be altered after a partitioned table has been created, you can reorganize your data to enhance frequent queries that may not have been so when the partitioning scheme was first set up. This capability, sometimes referred to as partition pruning, was implemented in MySQL 5.1.6. For more information, see Section 18.4, “Partition Pruning”.