Partitioning, new with MySQL 5.1, has complicated interactions with queries and indexes. If one isn’t careful it is easy to degrade performance. For example, select queries that go with that grain (queries where partition elimination occurs) can be much quicker, but select queries that go against that grain can be much slower. Queries that go against the grain must query each partition, so for a table with 12 partitions, one query against that table can result in 12 queries, one against each of the partitions. An example of this would be a query against a month partitioned table that is looking to see how much activity a product had in the past 12 months.
The ideal partitioning scheme would be a system where all queries only needs to access data from one[Read more...]