MySQL Forums
Forum List  »  Partitioning

Re: Dynamic Partition Pruning
Posted by: Sergey Petrunya
Date: December 10, 2006 11:52PM

Hi,

We have some equivalent of dynamic partition pruning. At the moment it is referred to as "partition selection" and it is not visible in EXPLAIN PARTITIONS output. It is documented at
http://dev.mysql.com/doc/internals/en/optimizer-partition-selection.html

For Niklas's query

explain partitions select * from t1 left join lookup on (t1.a=lookup.b) where lookup.a=2
+----+-------------+--------+-----------------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-----------------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | lookup | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 1 | SIMPLE | t1 | p1,p2,p3,p4,p_MAXIMUM | ref | PRIMARY | PRIMARY | 4 | const | 5 | |
+----+-------------+--------+-----------------------+-------+---------------+---------+---------+-------+------+-------+

The EXPLAIN shows that [static] partition pruning couldn't infer anything useful. This is a deficiency as we could infer that (lookup.a=2 AND t1.a=lookup.b) => t1.a=2 and infer the list of partitions to use.

This deficiency is only minor however as partition selection will be able to determine that for each "ref" access to table t1 we need to access only one partition. This is not visible in EXPLAIN at the moment, the only way to figure it out is to look at performance counters.

Btw, it is not a requirement that we get one row for the `lookup` table. The only requirement of partition selection is that ref-like access method is used and we can look at the lookup tuple and determine one single partition we need to access.

Sergey Petrunia, Software Developer
MySQL AB, www.mysql.com
My blog: http://s.petrunia.net/blog

Options: ReplyQuote


Subject
Views
Written By
Posted
5437
September 21, 2006 04:27AM
2648
December 09, 2006 08:25AM
Re: Dynamic Partition Pruning
3949
December 10, 2006 11:52PM
3918
December 15, 2006 07:45AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.