Is this MySql table a good candidate for partitioning?

Question

I have a table with ~1.9 million rows and growing consistently. I run some fairly complicated queries against this data. The active data is generally clustered toward the end of the table -- that is, only the most recent n% of the records tend to be accessed on a regular basis, although the rest of the data needs to be available in the same table for the less usual cases that people look back at the older records.

For those with partitioning experience in MySQL, does this table seem like it would be a good candidate for partitioning? Or is it just too small to get much gain?

Thanks,

Jared

p.s. I looked for a question on stackoverflow to answer this question, but didn't find anything that quite fit.

Answer 1

Check out this article...He shows significant gains on a table with only 3 columns and 800K records. As long as your partitioning on a column that produces either an integer or NULL you should see some great performance improvements. I loved the speed gains from date based partitioning that I have seen with significantly fewer records but more columns.

Improving Database Performance with Partitioning

Answer 2

Logically, yes, if you typically run queries that need only the most recent 2% of the table, this would be a great candidate for partitioning.

The biggest barrier to using MySQL partitioning is that the column you use for the partitioning key must be part of the primary key and any other unique keys. This practically makes some tables not possible to partition.

If this blocks you from partitioning the table, the fallback plan is to partition "manually." That is, make two real tables with identical structure. Every week (or whatever schedule you want), run a batch job to migrate the older data to the second table. You can always make a VIEW which is a UNION of the two tables, in case you need to run occasional table-scans.

Answer 3

Table size should be greater than 5 GB. You should go for RANGE PARTITIONING...(Monthly or yearly)