Re: Partitioning Limitations

[Rick James]

Partitioning is excellent for purging of old data (eg tossing data more than 30 days old).

Partitioning can help in some cases where a full table scan would otherwise be done. It limits the number of rows examined to a subset of the partitions. Still, it has to scan a significant number of rows. A full table scan, or anything approaching it, is a no-no in a production environment; I look for ways to reformulate the query and/or the schema. This rarely leads to partitioning.

Data Warehousing seems like an excellent application for Partitioning. I would argue that creating summary tables is a much better way to get performance out of DW.

Jon, I may sound like a skeptic, but I would actually love to have some examples where Partitioning provides better performance in production environments than other techniques. (The examples in the pruning DevZone pages did not impress me.)

When subqueries were first added to MySQL, I thought all subqueries could be turned into JOINs. Gradually, I learned
* some subqueries cannot be turned into JOINs.
* some subqueries provide better performance.
* MySQL sometimes cannot recognize optimizations that converting to JOIN would achieve.

Meanwhile, when someone quotes "queries on partitioned tables can be significantly faster", I respond with "do you have such queries?" and "can they be optimized other ways?".

The examples on the DevZone and pruning pages seem to center around pruning as a way of shrinking a table scan down to a range scan. I counter with

* Changing indexes (and WHERE) to do similar things in a plain table.

* Clustering the data (eg, as InnoDB does with its PK) can sometimes achieve as good or better results. (I did not notice any mention of clustering the data as a possible benefit of partitioning. I have heard of a benchmark showing a modest improvement from a careful partitioning of a multi-TB table.)

* If you need a long table/range scans have no place in the Web world. (OK, there are a lot of useful applications that don't require low latency.)