Databases Reference
In-Depth Information
select_type: SIMPLE
table: sales_by_day
partitions: p_2011,p_2012
The optimizer is pretty good about pruning; for example, it can convert ranges into
lists of discrete values and prune on each item in the list. However, it's not all-knowing.
The following
WHERE
clause is theoretically prunable, but MySQL can't prune it:
mysql>
EXPLAIN PARTITIONS SELECT * FROM sales_by_day WHERE YEAR(day) = 2010\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales_by_day
partitions: p_2010,p_2011,p_2012
MySQL can prune only on comparisons to the partitioning function's columns. It can-
not prune on the result of an expression, even if the expression is the same as the
partitioning function. This is similar to the way that indexed columns must be isolated
in the query to make the index usable (see
Chapter 5
). You can convert the query into
an equivalent form, though:
mysql>
EXPLAIN PARTITIONS SELECT * FROM sales_by_day
->
WHERE day BETWEEN '2010-01-01' AND '2010-12-31'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales_by_day
partitions: p_2010
Because the
WHERE
clause now refers directly to the partitioning column, not to an ex-
pression, the optimizer can prune out other partitions. The rule of thumb is that even
though you can partition by expressions, you must search by column.
The optimizer is smart enough to prune partitions during query processing, too. For
example, if a partitioned table is the second table in a join, and the join condition is the
partitioned key, MySQL will search for matching rows only in the relevant partitions.
(
EXPLAIN
won't show the partition pruning, because it happens at runtime, not at query
optimization time.)
Merge Tables
Merge tables are sort of an earlier, simpler kind of partitioning with different restrictions
and fewer optimizations. Whereas partitioning enforces the abstraction rigorously, de-
nying access to the underlying partitions and permitting you to reference only the par-
titioned table, merge tables let you access the underlying tables separately from the
merge table. And whereas partitioning is more integrated with the query optimizer and
is the way of the future, merge tables are quasi-deprecated and might even be removed
someday.
Like partitioned tables, merge tables are wrappers around underlying MyISAM tables
with the same structure. Although you can think of merge tables as an older, more
