MySQL Forums
Forum List  »  Partitioning

Re: Pruning with HASH or KEY
Posted by: Mikael Ronström
Date: August 21, 2006 01:59AM

Hi,


Shlomi Noach Wrote:
-------------------------------------------------------
> Hello, everyone,
>
> In the latest article on developer zone, "MySQL
> 5.1 New Features: MySQL Partitions", the writer
> says that "Pruning is good with RANGE and LIST
> partitions, irrelevant with HASH and KEY
> partitions".
>
> However, on MySQL 5.1 reference manual,
> http://mysql.com/doc/refman/5.1/en/partitioning-pr
> uning.html, it says: "For tables that are
> partitioned by HASH or KEY, partition pruning is
> also possible. However, it can be used only on
> integer columns of tables using these partitioning
> types..."
>
> Which of the two is correct?
>

Partition pruning for RANGE and LIST can use both equal and range based
partition pruning. HASH and KEY can always use equal-based partition pruning.
There is also a special range optimisations also for HASH and KEY partitioning.
This uses the fact that a short integer range can be evaluated as a list of
values, this optimisation is only used if the range size is smaller than the
number of partitions.

> I have a table my_table which I partition by HASH
> over an int field my_field (which is, accidently,
> one of the keys for the table - it has an index -
> but not the primary key).
> Will MySQL perform pruning when I query:
> SELECT * FROM my_table WHERE my_field = 1234 AND
> other_field = 5678 AND ...
> ?
>

Yep, should do so, actually this special variant is handled by the
partition handler to ensure that we only access one partition in cases
where an equal index lookup contains all partition fields.

> Is there a general way (such as in EXPLAIN SELECT)
> to know in advance if MySQL will perform pruning?
>

EXPLAIN PARTITIONS with the same syntax as EXPLAIN will add a list
of partitions to the EXPLAIN output.

Rgrds Mikael

> Thanks in advance,
> Shlomi

Options: ReplyQuote


Subject
Views
Written By
Posted
3058
August 19, 2006 12:50AM
Re: Pruning with HASH or KEY
2259
August 21, 2006 01:59AM
2037
August 22, 2006 04:52AM
2111
August 21, 2006 04:11AM
2066
August 22, 2006 04:51AM


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.