MySQL Optimization 20GB Table

Question

I have a table with 20 gigabytes in which a large number of attachments and updates are added daily. This table is also frequently viewed. I would like to know if MySQL indexes can become fragmented and may need to be rebuilt or something like that.

I find it difficult to determine which of the CHECK TABLE, REPAIR TABLE or something like that?

Any guidance appreciated, I db newb.

Answer 1

Depending on the version of MySQL you are using, you might consider creating table partitions. There are several ways you can use it, either by hashing identifiers, or by reconstructing a date.

There are also ways to logically separate “working” data from archived data. Consider a large table representing a feed. It is possible that data that is less than 14 or 28 days makes up 95% of your data used, and the rest can be placed in the archive table.

If it is possible to split into several servers representing pieces of data, there are some great presentations on how to create storage systems using MySQL. Brad Fitpatrick, LiveJournal review is a great place to start.

Depending on the type of data you are storing, you might not even need MySQL. If most search queries get a primary key, you can examine key / value storage systems like Redis or Cassandra to see if they fit your needs.