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