mysql partitions: double enum partitioning key

Question

I've got a table with lots of data with a compound primary key: node_id, ts (timestamp), geo_id and period_type_id. Last two are enums, in fact they are integers, but have limited set of possible values (only 1,2,3), there will never be new values there.

I'm thinking of a partitioning solution here and I read that partitioning works the best, when there's only one partition scanned for a given query. And this would take place when I partitioned the table by both of those enums. The question is - what should the alter table be in a case when I want to partition by two integer fields geo_id and period_type_id? There's a HASH, LIST and RANGE partitioning types (http://dev.mysql.com/doc/refman/5.1/en/partitioning-types.html), but I don't want to make a blind guess, since creating those partitions will take hours if not days (great lots of records).

Answer 1

If you won't have a million rows in the table, don't bother with PARTITIONing.

RANGE partitioning is about the only version that has a hope of providing performance improvements in real-world cases.

Let's see some of the SELECTs you want to use on the table. And let's see SHOW CREATE TABLE (without the partitioning). Between those, we can better advise you.