# # WL#6986 : Make switching of index due to order by limit cost based # # Testcase for Bug#16522053 CREATE TABLE t1 ( pk INT PRIMARY KEY AUTO_INCREMENT, i INT, j INT, INDEX (i), INDEX (j) ); INSERT INTO t1 (i,j) VALUES (1,1); set @d=1; INSERT INTO t1 (i,j) SELECT i+@d, j+@d from t1; set @d=@d*2; INSERT INTO t1 (i,j) SELECT i+@d, j+@d from t1; set @d=@d*2; INSERT INTO t1 (i,j) SELECT i+@d, j+@d from t1; set @d=@d*2; INSERT INTO t1 (i,j) SELECT i+@d, j+@d from t1; set @d=@d*2; INSERT INTO t1 (i,j) SELECT i+@d, j+@d from t1; set @d=@d*2; INSERT INTO t1 (i,j) SELECT i+@d, j+@d from t1; set @d=@d*2; INSERT INTO t1 (i,j) SELECT i+@d, j+@d from t1; set @d=@d*2; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK EXPLAIN SELECT * FROM t1 WHERE i<100 AND j<10 ORDER BY i LIMIT 5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range i,j i 5 NULL 1 5.00 Using index condition; Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i` AS `i`,`test`.`t1`.`j` AS `j` from `test`.`t1` where ((`test`.`t1`.`i` < 100) and (`test`.`t1`.`j` < 10)) order by `test`.`t1`.`i` limit 5 SELECT * FROM t1 WHERE i<100 AND j<10 ORDER BY i LIMIT 5; pk i j 1 1 1 2 2 2 3 3 3 4 4 4 5 5 5 DROP TABLE t1; CREATE TABLE t0 ( i0 INTEGER NOT NULL ); INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); CREATE TABLE t1 ( pk INTEGER PRIMARY KEY, i1 INTEGER NOT NULL, i2 INTEGER NOT NULL, INDEX k1 (i1), INDEX k2 (i1,i2) ) ENGINE=XENGINE; INSERT INTO t1 SELECT a0.i0 + 10*a1.i0 + 100*a0.i0 + 1000*a1.i0, (a0.i0 + 10*a1.i0 + 100*a0.i0 + 1000*a1.i0) % 1000, (a0.i0 + 10*a1.i0 + 100*a0.i0 + 1000*a1.i0) % 1000 FROM t0 AS a0, t0 AS a1; CREATE TABLE t2 ( pk INTEGER PRIMARY KEY, i1 INTEGER NOT NULL, INDEX k1 (i1) ) ENGINE=XENGINE; INSERT INTO t2 SELECT a0.i0 + 10*a1.i0 + 100*a0.i0 + 1000*a1.i0, (a0.i0 + 10*a1.i0 + 100*a0.i0 + 1000*a1.i0) % 500 FROM t0 AS a0, t0 AS a1; ANALYZE TABLE t1,t2; Table Op Msg_type Msg_text test.t1 analyze status OK test.t2 analyze status OK # Query should use index to optimize the ORDER BY LIMIT EXPLAIN SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.i1=t2.i1 WHERE t1.pk > 7000 and t1.i1 > 2 ORDER BY t1.i1 LIMIT 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range PRIMARY,k1,k2 k2 4 NULL 1 5.00 Using where; Using index 1 SIMPLE t2 NULL ref k1 k1 4 test.t1.i1 2 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`i2` AS `i2`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`i1` AS `i1` from `test`.`t1` straight_join `test`.`t2` where ((`test`.`t2`.`i1` = `test`.`t1`.`i1`) and (`test`.`t1`.`pk` > 7000) and (`test`.`t1`.`i1` > 2)) order by `test`.`t1`.`i1` limit 2 # Query should not make the switch to use index to # optimize ORDER BY LIMIT. So should be using filesort EXPLAIN SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.i1=t2.i1 WHERE t1.pk > 7000 and t1.i1 > 2 ORDER BY t1.i1 LIMIT 5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range PRIMARY,k1,k2 k2 4 NULL 1 5.00 Using where; Using index 1 SIMPLE t2 NULL ref k1 k1 4 test.t1.i1 2 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`i2` AS `i2`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`i1` AS `i1` from `test`.`t1` straight_join `test`.`t2` where ((`test`.`t2`.`i1` = `test`.`t1`.`i1`) and (`test`.`t1`.`pk` > 7000) and (`test`.`t1`.`i1` > 2)) order by `test`.`t1`.`i1` limit 5 SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.i1=t2.i1 WHERE t1.pk > 7000 and t1.i1 > 2 ORDER BY t1.i1 LIMIT 5; pk i1 i2 pk i1 7070 70 70 7070 70 8080 80 80 8080 80 9090 90 90 9090 90 7171 171 171 7171 171 8181 181 181 8181 181 # Changing range condition on i1 should make # key on i1 get picked to give the order EXPLAIN SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.i1=t2.i1 WHERE t1.pk > 7000 and t1.i1 > 800 ORDER BY t1.i1 LIMIT 5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range PRIMARY,k1,k2 k2 4 NULL 1 5.00 Using where; Using index 1 SIMPLE t2 NULL ref k1 k1 4 test.t1.i1 2 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`i2` AS `i2`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`i1` AS `i1` from `test`.`t1` straight_join `test`.`t2` where ((`test`.`t2`.`i1` = `test`.`t1`.`i1`) and (`test`.`t1`.`pk` > 7000) and (`test`.`t1`.`i1` > 800)) order by `test`.`t1`.`i1` limit 5 SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.i1=t2.i1 WHERE t1.pk > 7000 and t1.i1 > 800 ORDER BY t1.i1 LIMIT 5; pk i1 i2 pk i1 # Use range condition only on pk to see if switch # happens just for ORDER BY LIMIT # Should not happen unless range results in too # many records EXPLAIN SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.i1=t2.i1 WHERE t1.pk > 7000 ORDER BY t1.i1 LIMIT 5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range PRIMARY,k1,k2 PRIMARY 4 NULL 1 100.00 Using where; Using filesort 1 SIMPLE t2 NULL ref k1 k1 4 test.t1.i1 2 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`i2` AS `i2`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`i1` AS `i1` from `test`.`t1` straight_join `test`.`t2` where ((`test`.`t2`.`i1` = `test`.`t1`.`i1`) and (`test`.`t1`.`pk` > 7000)) order by `test`.`t1`.`i1` limit 5 SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.i1=t2.i1 WHERE t1.pk > 7000 ORDER BY t1.i1 LIMIT 5; pk i1 i2 pk i1 7070 70 70 7070 70 8080 80 80 8080 80 9090 90 90 9090 90 7171 171 171 7171 171 8181 181 181 8181 181 EXPLAIN SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.i1=t2.i1 WHERE t1.pk > 1000 ORDER BY t1.i1 LIMIT 5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range PRIMARY,k1,k2 PRIMARY 4 NULL 1 100.00 Using where; Using filesort 1 SIMPLE t2 NULL ref k1 k1 4 test.t1.i1 2 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`i2` AS `i2`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`i1` AS `i1` from `test`.`t1` straight_join `test`.`t2` where ((`test`.`t2`.`i1` = `test`.`t1`.`i1`) and (`test`.`t1`.`pk` > 1000)) order by `test`.`t1`.`i1` limit 5 SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.i1=t2.i1 WHERE t1.pk > 1000 ORDER BY t1.i1 LIMIT 5; pk i1 i2 pk i1 1010 10 10 1010 10 2020 20 20 2020 20 3030 30 30 3030 30 4040 40 40 4040 40 5050 50 50 5050 50 # check if switch happens when the index for order # by is non-covering EXPLAIN SELECT t1.i1,t1.i2 FROM t1 STRAIGHT_JOIN t2 ON t1.i1=t2.i1 WHERE t1.pk > 100 ORDER BY t1.i1 LIMIT 5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range PRIMARY,k1,k2 PRIMARY 4 NULL 1 100.00 Using where; Using filesort 1 SIMPLE t2 NULL ref k1 k1 4 test.t1.i1 2 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`i1` AS `i1`,`test`.`t1`.`i2` AS `i2` from `test`.`t1` straight_join `test`.`t2` where ((`test`.`t2`.`i1` = `test`.`t1`.`i1`) and (`test`.`t1`.`pk` > 100)) order by `test`.`t1`.`i1` limit 5 SELECT t1.i1,t1.i2 FROM t1 STRAIGHT_JOIN t2 ON t1.i1=t2.i1 WHERE t1.pk > 100 ORDER BY t1.i1 LIMIT 5; i1 i2 10 10 20 20 30 30 40 40 50 50 # Reduce the fanout for table t2 and check # that index for order by is not choosen EXPLAIN SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.i1=t2.i1 WHERE t1.pk > 7000 and t2.pk = 100 ORDER BY t1.i1 LIMIT 5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range PRIMARY,k1,k2 PRIMARY 4 NULL 1 100.00 Using where; Using filesort 1 SIMPLE t2 NULL const PRIMARY,k1 PRIMARY 4 const 1 5.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`i2` AS `i2`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`i1` AS `i1` from `test`.`t1` straight_join `test`.`t2` where ((`test`.`t2`.`i1` = `test`.`t1`.`i1`) and (`test`.`t2`.`pk` = 100) and (`test`.`t1`.`pk` > 7000)) order by `test`.`t1`.`i1` limit 5 SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.i1=t2.i1 WHERE t1.pk > 7000 and t2.pk = 100 ORDER BY t1.i1 LIMIT 5; pk i1 i2 pk i1 # Increase the fanout to see if index gets choosen # for order by for which range scan was choosen # earlier INSERT INTO t2 SELECT a0.i0 + 10*a1.i0 + 100*a0.i0 + 1000*a1.i0 + 1, (a0.i0 + 10*a1.i0 + 100*a0.i0 + 1000*a1.i0) % 500 FROM t0 AS a0, t0 AS a1; ANALYZE TABLE t2; Table Op Msg_type Msg_text test.t2 analyze status OK EXPLAIN SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.i1=t2.i1 WHERE t1.pk > 7000 ORDER BY t1.i1 LIMIT 5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range PRIMARY,k1,k2 PRIMARY 4 NULL 1 100.00 Using where; Using filesort 1 SIMPLE t2 NULL ref k1 k1 4 test.t1.i1 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`i2` AS `i2`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`i1` AS `i1` from `test`.`t1` straight_join `test`.`t2` where ((`test`.`t2`.`i1` = `test`.`t1`.`i1`) and (`test`.`t1`.`pk` > 7000)) order by `test`.`t1`.`i1` limit 5 SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.i1=t2.i1 WHERE t1.pk > 7000 ORDER BY t1.i1 LIMIT 5; pk i1 i2 pk i1 7070 70 70 7070 70 7070 70 70 7071 70 8080 80 80 8080 80 8080 80 80 8081 80 9090 90 90 9090 90 # Check if the index for order by is used when # force index is done on order by EXPLAIN SELECT * FROM t1 FORCE INDEX FOR ORDER BY (k2) STRAIGHT_JOIN t2 ON t1.i1=t2.i1 WHERE t1.pk > 7000 ORDER BY t1.i1 LIMIT 5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range PRIMARY,k1,k2 PRIMARY 4 NULL 1 100.00 Using where; Using filesort 1 SIMPLE t2 NULL ref k1 k1 4 test.t1.i1 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`i2` AS `i2`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`i1` AS `i1` from `test`.`t1` FORCE INDEX FOR ORDER BY (`k2`) straight_join `test`.`t2` where ((`test`.`t2`.`i1` = `test`.`t1`.`i1`) and (`test`.`t1`.`pk` > 7000)) order by `test`.`t1`.`i1` limit 5 SELECT * FROM t1 FORCE INDEX FOR ORDER BY (k2) STRAIGHT_JOIN t2 ON t1.i1=t2.i1 WHERE t1.pk > 7000 ORDER BY t1.i1 LIMIT 5; pk i1 i2 pk i1 7070 70 70 7070 70 7070 70 70 7071 70 8080 80 80 8080 80 8080 80 80 8081 80 9090 90 90 9090 90 DROP TABLE t0, t1, t2; # # Bug #26483909: INCORRECT BEHAVIOR FOR QUERY WITH DISTINCT # and ORDER BY ... LIMIT. # CREATE TABLE t1 ( pk int(11) NOT NULL, col_int int(11), col_varchar_key varchar(20), PRIMARY KEY (pk), KEY col_varchar_key (col_varchar_key), KEY col_varchar_key_2 (col_varchar_key(5)) ); Warnings: Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. INSERT INTO t1 VALUES (1,2,'t'), (2,5,'efqsdksj'), (3,NULL,'fqsdksjijcs'),(4,8,'qsdksjijc'), (5,40,NULL),(6,3,'sdksji'),(7,2,NULL), (8,3,'dks'),(9,0,'ksjijcsz'), (10,84,'sjijcszxwbjj'); ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK SET @@SESSION.sql_mode='NO_ENGINE_SUBSTITUTION'; EXPLAIN SELECT DISTINCT col_int FROM t1 WHERE col_varchar_key <> 'c' OR col_varchar_key > 'w' ORDER BY col_varchar_key LIMIT 100; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range col_varchar_key,col_varchar_key_2 col_varchar_key 83 NULL 2 50.00 Using index condition; Using temporary Warnings: Note 1003 /* select#1 */ select distinct `test`.`t1`.`col_int` AS `col_int` from `test`.`t1` where ((`test`.`t1`.`col_varchar_key` <> 'c') or (`test`.`t1`.`col_varchar_key` > 'w')) order by `test`.`t1`.`col_varchar_key` limit 100 SELECT DISTINCT col_int FROM t1 WHERE col_varchar_key <> 'c' OR col_varchar_key > 'w' ORDER BY col_varchar_key LIMIT 100; col_int 3 5 NULL 0 8 84 2 DROP TABLE t1;