Re: KEY vs RANGE partitioning

[Rick James]

SHOW CREATE TABLE

Sorted by popularity? Is that a column? Is it indexed? Does the value change?

How big is the table (SHOW TABLE STATUS)? If it is not at least 50MB, don't bother with PARTITIONing.

MyISAM or InnoDB?

> MySQL reads the various partitions in parallel
It does NOT!

> will help me scan 90% of the times only one partition of the table
That depends on the partition key you choose together with the WHERE clause in the SELECTs.

> I use RAID 10
That is good -- but totally independent of the PARTITION question.

Will you be having multiple connections at the same time? That is the only parallelism you can get (in general). RAID striping, PARTITIONs, multiple cores or cpus -- these are (mostly) useless for a single thread.

I see virtually no use for PARTITION types other than RANGE.