=== modified file 'mysql-test/r/partition_innodb.result' --- mysql-test/r/partition_innodb.result 2013-02-14 16:08:56 +0000 +++ mysql-test/r/partition_innodb.result 2013-09-10 13:03:35 +0000 @@ -26,7 +26,7 @@ Table Op Msg_type Msg_text test.t1 analyze status OK EXPLAIN SELECT b FROM t1 WHERE b between 'L' and 'N' AND a > -100; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,b b 67 NULL 18 Using where; Using index +1 SIMPLE t1 range PRIMARY,b b 67 NULL 34 Using where; Using index DROP TABLE t1; # # Bug#13007154: Crash in keys_to_use_for_scanning with ORDER BY === modified file 'mysql-test/r/partition_pruning.result' --- mysql-test/r/partition_pruning.result 2013-05-01 07:13:04 +0000 +++ mysql-test/r/partition_pruning.result 2013-09-10 13:02:50 +0000 @@ -18,7 +18,7 @@ id select_type table partitions type pos # # # # # # # # # 3 # EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7; id select_type table partitions type possible_keys key key_len ref rows Extra -# # # # # # # # # 10 # +# # # # # # # # # 9 # EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1; id select_type table partitions type possible_keys key key_len ref rows Extra # # # # # # # # # 3 # @@ -105,7 +105,7 @@ a 6 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index +1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max range PRIMARY PRIMARY 4 NULL 9 Using where; Using index SELECT * FROM t1 WHERE a <= 1; a -1 @@ -168,7 +168,7 @@ a 6 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 6; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index +1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max range PRIMARY PRIMARY 4 NULL 9 Using where; Using index SELECT * FROM t1 WHERE a <= 7; a -1 @@ -182,7 +182,7 @@ a 7 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 7; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index +1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max range PRIMARY PRIMARY 4 NULL 9 Using where; Using index SELECT * FROM t1 WHERE a = 1; a 1 @@ -424,7 +424,7 @@ a 5 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 6; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0,p1,p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index +1 SIMPLE t1 p0,p1,p2,p3,p4,max range PRIMARY PRIMARY 4 NULL 8 Using where; Using index SELECT * FROM t1 WHERE a <= 1; a -1 @@ -474,7 +474,7 @@ a 5 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 5; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0,p1,p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index +1 SIMPLE t1 p0,p1,p2,p3,p4,max range PRIMARY PRIMARY 4 NULL 8 Using where; Using index SELECT * FROM t1 WHERE a <= 6; a -1 @@ -487,7 +487,7 @@ a 6 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 6; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0,p1,p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index +1 SIMPLE t1 p0,p1,p2,p3,p4,max range PRIMARY PRIMARY 4 NULL 8 Using where; Using index SELECT * FROM t1 WHERE a = 1; a 1 @@ -744,13 +744,13 @@ a 1001-01-01 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p2001-01-01 range a a 4 NULL 3 Using where; Using index @@ -759,26 +759,26 @@ id select_type table partitions type pos 1 SIMPLE t1 p1001-01-01 system a NULL NULL NULL 1 NULL EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL ref a a 4 const 1 Using index # Disabling warnings for the invalid date EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p2001-01-01 index a a 4 NULL 4 Using where; Using index @@ -790,16 +790,16 @@ id select_type table partitions type pos 1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 index a a 4 NULL 5 Using where; Using index +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index # test without index ALTER TABLE t1 DROP KEY a; SELECT * FROM t1 WHERE a < '1001-01-01'; @@ -1076,7 +1076,7 @@ id select_type table partitions type pos 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index @@ -1104,10 +1104,10 @@ id select_type table partitions type pos # Disabling warnings for the invalid date EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL index a a 4 NULL 4 Using where; Using index @@ -1119,10 +1119,10 @@ id select_type table partitions type pos 1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p1001-01-01 index a a 4 NULL 4 Using where; Using index @@ -1405,7 +1405,7 @@ id select_type table partitions type pos 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index @@ -1433,10 +1433,10 @@ id select_type table partitions type pos # Disabling warnings for the invalid date EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL index a a 4 NULL 4 Using where; Using index @@ -1448,10 +1448,10 @@ id select_type table partitions type pos 1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p1001-01-01 index a a 4 NULL 4 Using where; Using index @@ -3082,18 +3082,18 @@ id select_type table partitions type pos 1 SIMPLE t2 p0,p1,p2 ALL NULL NULL NULL NULL 510 Using where explain partitions select * from t2 where b = 4; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 96 NULL +1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76 NULL explain extended select * from t2 where b = 6; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ref b b 5 const 96 100.00 NULL +1 SIMPLE t2 ref b b 5 const 76 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = 6) explain partitions select * from t2 where b = 6; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 96 NULL +1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76 NULL explain extended select * from t2 where b in (1,3,5); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL b NULL NULL NULL 910 51.65 Using where +1 SIMPLE t2 ALL b NULL NULL NULL 910 40.66 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (1,3,5)) explain partitions select * from t2 where b in (1,3,5); @@ -3101,7 +3101,7 @@ id select_type table partitions type pos 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where explain extended select * from t2 where b in (2,4,6); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL b NULL NULL NULL 910 31.65 Using where +1 SIMPLE t2 ALL b NULL NULL NULL 910 25.05 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (2,4,6)) explain partitions select * from t2 where b in (2,4,6); @@ -3109,7 +3109,7 @@ id select_type table partitions type pos 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where explain extended select * from t2 where b in (7,8,9); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL b NULL NULL NULL 910 19.12 Using where +1 SIMPLE t2 ALL b NULL NULL NULL 910 36.70 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (7,8,9)) explain partitions select * from t2 where b in (7,8,9); @@ -3117,15 +3117,15 @@ id select_type table partitions type pos 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where explain extended select * from t2 where b > 5; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL b NULL NULL NULL 910 29.23 Using where +1 SIMPLE t2 range b b 5 NULL 14 100.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` > 5) explain partitions select * from t2 where b > 5; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where +1 SIMPLE t2 p0,p1,p2,p3,p4 range b b 5 NULL 14 Using where explain extended select * from t2 where b > 5 and b < 8; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL b NULL NULL NULL 910 28.13 Using where +1 SIMPLE t2 ALL b NULL NULL NULL 910 22.09 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 5) and (`test`.`t2`.`b` < 8)) explain partitions select * from t2 where b > 5 and b < 8; @@ -3133,15 +3133,15 @@ id select_type table partitions type pos 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where explain extended select * from t2 where b > 5 and b < 7; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range b b 5 NULL 96 100.00 Using where +1 SIMPLE t2 range b b 5 NULL 76 100.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 5) and (`test`.`t2`.`b` < 7)) explain partitions select * from t2 where b > 5 and b < 7; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 p0,p1,p2,p3,p4 range b b 5 NULL 96 Using where +1 SIMPLE t2 p0,p1,p2,p3,p4 range b b 5 NULL 76 Using where explain extended select * from t2 where b > 0 and b < 5; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL b NULL NULL NULL 910 53.19 Using where +1 SIMPLE t2 ALL b NULL NULL NULL 910 73.85 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 0) and (`test`.`t2`.`b` < 5)) explain partitions select * from t2 where b > 0 and b < 5; @@ -3174,7 +3174,7 @@ Variable_name Value Handler_read_key 0 explain partitions update t2 set a = 222 where b = 7; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 p0,p1,p2,p3,p4 range b b 5 const 4 Using where; Using temporary +1 SIMPLE t2 p0,p1,p2,p3,p4 range b b 5 const 102 Using where; Using temporary flush status; update t2 set a = 222 where b = 7; show status like 'Handler_read_rnd_next'; @@ -3185,18 +3185,18 @@ Variable_name Value Handler_read_key 5 explain extended delete from t2 where b = 7; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL b NULL NULL NULL 910 100.00 Using where +1 SIMPLE t2 range b b 5 const 139 100.00 Using where explain partitions delete from t2 where b = 7; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where +1 SIMPLE t2 p0,p1,p2,p3,p4 range b b 5 const 139 Using where flush status; delete from t2 where b = 7; show status like 'Handler_read_rnd_next'; Variable_name Value -Handler_read_rnd_next 1215 +Handler_read_rnd_next 0 show status like 'Handler_read_key'; Variable_name Value -Handler_read_key 0 +Handler_read_key 5 explain partitions delete from t2 where b > 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 810 Using where === modified file 'sql/ha_partition.cc' --- sql/ha_partition.cc 2013-08-19 06:51:20 +0000 +++ sql/ha_partition.cc 2013-09-10 12:28:41 +0000 @@ -7170,53 +7170,27 @@ const key_map *ha_partition::keys_to_use } -/** - Minimum number of rows to base optimizer estimate on. -*/ - -ha_rows ha_partition::min_rows_for_estimate() -{ - uint i, max_used_partitions, tot_used_partitions; - DBUG_ENTER("ha_partition::min_rows_for_estimate"); +/* + Return time for a scan of the table - tot_used_partitions= bitmap_bits_set(&m_part_info->read_partitions); + SYNOPSIS + scan_time() - /* - All partitions might have been left as unused during partition pruning - due to, for example, an impossible WHERE condition. Nonetheless, the - optimizer might still attempt to perform (e.g. range) analysis where an - estimate of the the number of rows is calculated using records_in_range. - Hence, to handle this and other possible cases, use zero as the minimum - number of rows to base the estimate on if no partition is being used. - */ - if (!tot_used_partitions) - DBUG_RETURN(0); + RETURN VALUE + time for scan +*/ - /* - Allow O(log2(tot_partitions)) increase in number of used partitions. - This gives O(tot_rows/log2(tot_partitions)) rows to base the estimate on. - I.e when the total number of partitions doubles, allow one more - partition to be checked. - */ - i= 2; - max_used_partitions= 1; - while (i < m_tot_parts) - { - max_used_partitions++; - i= i << 1; - } - if (max_used_partitions > tot_used_partitions) - max_used_partitions= tot_used_partitions; +double ha_partition::scan_time() +{ + double scan_time= 0; + uint i; + DBUG_ENTER("ha_partition::scan_time"); - /* stats.records is already updated by the info(HA_STATUS_VARIABLE) call. */ - DBUG_PRINT("info", ("max_used_partitions: %u tot_rows: %lu", - max_used_partitions, - (ulong) stats.records)); - DBUG_PRINT("info", ("tot_used_partitions: %u min_rows_to_check: %lu", - tot_used_partitions, - (ulong) stats.records * max_used_partitions - / tot_used_partitions)); - DBUG_RETURN(stats.records * max_used_partitions / tot_used_partitions); + for (i= bitmap_get_first_set(&m_part_info->read_partitions); + i < m_tot_parts; + i= bitmap_get_next_set(&m_part_info->read_partitions, i)) + scan_time+= m_file[i]->scan_time(); + DBUG_RETURN(scan_time); } @@ -7249,30 +7223,6 @@ uint ha_partition::get_biggest_used_part } -/* - Return time for a scan of the table - - SYNOPSIS - scan_time() - - RETURN VALUE - time for scan -*/ - -double ha_partition::scan_time() -{ - double scan_time= 0; - uint i; - DBUG_ENTER("ha_partition::scan_time"); - - for (i= bitmap_get_first_set(&m_part_info->read_partitions); - i < m_tot_parts; - i= bitmap_get_next_set(&m_part_info->read_partitions, i)) - scan_time+= m_file[i]->scan_time(); - DBUG_RETURN(scan_time); -} - - /** Find number of records in a range. @param inx Index number @@ -7289,12 +7239,42 @@ double ha_partition::scan_time() ha_rows ha_partition::records_in_range(uint inx, key_range *min_key, key_range *max_key) { - ha_rows min_rows_to_check, rows, estimated_rows=0, checked_rows= 0; - uint partition_index= 0, part_id; + ha_rows rows, estimated_rows=0, checked_rows= 0; + uint partition_index= 0, part_id, number_of_one_row_estimates= 0; + uint i, partitions_for_estimate, tot_used_partitions, partitions_checked; DBUG_ENTER("ha_partition::records_in_range"); - min_rows_to_check= min_rows_for_estimate(); + tot_used_partitions= bitmap_bits_set(&m_part_info->read_partitions); + /* + All partitions might have been left as unused during partition pruning + due to, for example, an impossible WHERE condition. Nonetheless, the + optimizer might still attempt to perform (e.g. range) analysis where an + estimate of the the number of rows is calculated. + Hence, to handle this and other possible cases, return zero as estimate. + */ + if (!tot_used_partitions) + DBUG_RETURN(0); + + /* + Allow O(log2(tot_partitions)) increase in number of used partitions. + This gives O(tot_rows/log2(tot_partitions)) rows to base the estimate on. + I.e. when the total number of partitions doubles, allow one more + partition to be checked. + */ + i= 2; + partitions_for_estimate= 1; + while (i < m_tot_parts) + { + partitions_for_estimate++; + i= i << 1; + } + if (partitions_for_estimate > tot_used_partitions) + partitions_for_estimate= tot_used_partitions; + DBUG_PRINT("info", ("minimum partitions to use for estimate: %lu", + (ulong) partitions_for_estimate)); + + partitions_checked= 0; while ((part_id= get_biggest_used_partition(&partition_index)) != NO_CURRENT_PART_ID) { @@ -7305,15 +7285,27 @@ ha_rows ha_partition::records_in_range(u if (rows == HA_POS_ERROR) DBUG_RETURN(HA_POS_ERROR); - estimated_rows+= rows; + checked_rows+= m_file[part_id]->stats.records; + + if (rows > 1) + { + estimated_rows+= rows; + partitions_checked++; + } + else + { + number_of_one_row_estimates+= rows; + } + /* Returning 0 means no rows can be found, so we must continue this loop as long as we have estimated_rows == 0. - Also many engines return 1 to indicate that there may exist - a matching row, we do not normalize this by dividing by number of - used partitions, but leave it to be returned as a sum, which will - reflect that we will need to scan each partition's index. + Also many engines return 1 to indicate it may exist a matching row. + In these two cases, we do not consider the partition as checked. + We do not normalize the partitions that returns 1 by dividing + by number of used partitions, but leave it to be returned as a sum, + which will reflect that we will need to scan each partition's index. Note that this statistics may not always be correct, so we must continue even if the current partition has 0 rows, since we might have @@ -7321,22 +7313,26 @@ ha_rows ha_partition::records_in_range(u partition. */ if (estimated_rows && checked_rows && - checked_rows >= min_rows_to_check) + partitions_checked >= partitions_for_estimate) { DBUG_PRINT("info", - ("records_in_range(inx %u): %lu (%lu * %lu / %lu)", + ("records_in_range(inx %u): %lu ((%lu + %u) * %lu / %lu)", inx, - (ulong) (estimated_rows * stats.records / checked_rows), + (ulong) ((estimated_rows + number_of_one_row_estimates) * + stats.records / checked_rows), (ulong) estimated_rows, + number_of_one_row_estimates, (ulong) stats.records, (ulong) checked_rows)); - DBUG_RETURN(estimated_rows * stats.records / checked_rows); + DBUG_RETURN((estimated_rows + number_of_one_row_estimates) * + stats.records / checked_rows); } + i++; } DBUG_PRINT("info", ("records_in_range(inx %u): %lu", inx, - (ulong) estimated_rows)); - DBUG_RETURN(estimated_rows); + (ulong) estimated_rows + number_of_one_row_estimates)); + DBUG_RETURN(estimated_rows + number_of_one_row_estimates); } === modified file 'sql/ha_partition.h' --- sql/ha_partition.h 2013-06-14 06:11:32 +0000 +++ sql/ha_partition.h 2013-09-09 20:58:11 +0000 @@ -683,7 +683,6 @@ public: private: /* Helper functions for optimizer hints. */ - ha_rows min_rows_for_estimate(); uint get_biggest_used_partition(uint *part_index); public: