MySQL Optimization 20 gig table

Question

I have a 20 gig table that has a large amount of inserts and updates daily. This table is also frequently searched. I'd like to know if the MySQL indices can become fragmented and perhaps need to be rebuilt or something similar.

I'm finding it difficult to figure out which of the CHECK TABLE, REPAIR TABLE or something similar?

Any guidance appreciated, I'm a db newb.

Answer 1

Depending on the version of MySQL you are using, you could consider creating table partitions. There are several ways that you can go about using it, either by doing id hash paritioning or date paritioning.

There are also ways to logically seperate 'working' data from archival data. Consider a large table that represents a feed. It may be that data less than 14 or 28 days old is 95% of your used data and the rest can routinely be put in an archive table.

If being able to partition into multiple servers representing chunks of data is possible, there are some great presentations about how to create federated data storage systems using MySQL. Brad Fitpatrick's overview of LiveJournal is a great place to start.

Depending on the type of data you are storing, you may not even need MySQL at all. If most of your lookups are primary key gets, you may want to investigate key/value storage systems like Redis or Cassandra to see if they meet your needs.