Bug #72600 similar query ranges involve different partitions
Submitted: 9 May 2014 22:17 Modified: 13 May 2014 23:39
Reporter: K T Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.5-5.7 OS:Any
Assigned to: CPU Architecture:Any
Tags: mysql partitions select explain

[9 May 2014 22:17] K T
Mysql uses all table partitions in case if selected range is longer than 100 records

So either "explain partitions" is not working correctly or mysql handle queries to the partitioned tables wrong way... 

How to repeat:
so I have a table: 
CREATE TABLE `test_table` ( 
`test_id` int(10) unsigned NOT NULL, 
PRIMARY KEY (`test_id`)
PARTITION BY HASH ( test_id DIV 1000) 
filled with random values, etc.. 

and now our tests: 

query 1: 
FROM test_table 
WHERE test_id BETWEEN 1 AND 999; 

result 1: 
.. partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33,p34,p35,p36,p37,p38,p39,p40,p41,p42,p43,p44,p45,p46,p47,p48,p49 

query 2: 

FROM test_table 
(test_id >= 1 AND test_id <= 100) OR 
(test_id >= 101 AND test_id <= 200) OR 
(test_id >= 201 AND test_id <= 300) OR 
(test_id >= 301 AND test_id <= 400) OR 
(test_id >= 401 AND test_id <= 500) OR 
(test_id >= 501 AND test_id <= 600) OR 
(test_id >= 601 AND test_id <= 700) OR 
(test_id >= 701 AND test_id <= 800) OR 
(test_id >= 801 AND test_id <= 900) OR 
(test_id >= 901 AND test_id <= 999) 

result 2: 
.. partitions: 
[13 May 2014 13:03] MySQL Verification Team

Can you change your second query to be in this form:

FROM test_table 
(test_id BETWEEN 1 AND 100) OR 
(test_id BETWEEN 101 AND 200) OR 
(test_id BETWEEN 201 AND 300) OR 
(test_id BETWEEN  301 AND 400) OR 
(test_id BETWEEN  401 AND 500) OR 
(test_id BETWEEN 501 AND 600) OR 
(test_id BETWEEN 601 AND  700) OR 
(test_id BETWEEN 701 AND 800) OR 
(test_id BETWEEN  801 AND 900) OR 
(test_id BETWEEN  901 AND  999) 

Please, let us know the result.

If you get again a result of only `p0` from the EXPLAIN, then please make a dump of that table, gzip it or zip it and upload it to this bug via the "Files" tag.

The reason why we need a dump is because "... fill it up with random values  ..." is too imprecise in order to guarantee a repetition of the result.

Thanks a lot in advance.
[13 May 2014 13:21] K T
test_table dump

Attachment: mysql_bug.zip (application/octet-stream, text), 603 bytes.

[13 May 2014 13:22] K T
Ok. I created a table with just a few records, but this table still represents this bug. And yes - I get just "p0" for your modified query too.
[13 May 2014 16:18] MySQL Verification Team
Indeed, this is a bug in partition pruning. Fully verified !!!

I get the same difference in partition pruning results. Here is just the important output:

id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  test_partitions_table   p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33,p34,p35,p36,p37,p38,p39,p40,p41,p42,p43,p44,p45,p46,p47,p48,p49   index   PRIMARY PRIMARY 4       NULL    22  Using where; Using index
id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  test_partitions_table   p0      range   PRIMARY PRIMARY 4       NULL    19      Using where; Using index
[13 May 2014 23:39] Mattias Jonsson
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/refman/5.6/en/partitioning-pruning.html especially the section 'For tables that are partitioned by HASH or KEY, partition pruning is also possible in cases in which the WHERE clause uses a simple = relation against a column used in the partitioning expression.'

The reason for pruning to work with ranges <= 2*<number of partitions> is an optimization that enumerates all values in the range (search for MAX_RANGE_TO_WALK in sql/sql_partition.cc for implementation). If you want better range pruning I suggest 'PARTITION BY RANGE (test_id)' instead, which is optimized for pruning ranges.
[13 May 2014 23:42] Mattias Jonsson
Also note that p0 is also included in the 'BETWEEN 0 AND 999' range, so the result will still be correct, but all partitions will be search instead of only the matching partition.