MySQL Bugs: #72600: similar query ranges involve different partitions

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:	None
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

Description:
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`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 
PARTITION BY HASH ( test_id DIV 1000) 
PARTITIONS 50; 
------- 
filled with random values, etc.. 

and now our tests: 

---- 
query 1: 
EXPLAIN PARTITIONS SELECT test_id 
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: 

EXPLAIN PARTITIONS SELECT test_id 
FROM test_table 
WHERE ( 
(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: 
p0

Hi,

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

EXPLAIN PARTITIONS SELECT test_id 
FROM test_table 
WHERE ( 
(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.

test_table dump

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

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.

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

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.

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.