MySQL table size

If I had a table with 500 million rows and good indexing would the DB suffer any performance degredation? I am wondering if I need to shard table over multiple DBs even if they might be on the same server. Each row might be 1k in size as it has a cou开发者_如何转开发ple of text fields. Even if I foreign keyed them they would still be 500 million row table that comprises of say 700 bytes of data.

Also, any tips on engine selection for tables of this size would be great. Any advise would be greatly appreciated.

Cheers

Rich

MySQL supports user-defined partitioning for large tables.

As for whether or not you need it, it depends on your application and what queries you make. Have a look at the advantages of partitioning and see if any of them apply to your situation:

• Partitioning makes it possible to store more data in one table than can be held on a single disk or file system partition.

• Data that loses its usefulness can often be easily removed from a partitioned table by dropping the partition (or partitions) containing only that data. Conversely, the process of adding new data can in some cases be greatly facilitated by adding one or more new partitions for storing specifically that data.

• 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, which automatically 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 often used when the partitioning scheme was first set up. This ability to exclude non-matching partitions (and thus any rows they contain) is often referred to as partition pruning, and was implemented in MySQL 5.1.6. For more information, see Section 18.4, “Partition Pruning”.

I've experienced that having over 10 million records in an InnoDB table can be very slow when the table is joined, even with proper indices. Of you're just selecting from this table alone, without joins, grouping or sorting, it might just work.