Edit File: single_delete_update.result
# # Bug #30584: delete with order by and limit clauses does not use # limit efficiently # CREATE TABLE t1 (i INT); INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), (20),(21),(22),(23),(24),(25); CREATE TABLE t2(a INT, i INT PRIMARY KEY); INSERT INTO t2 (i) SELECT i FROM t1; FLUSH STATUS; SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; a i NULL 11 NULL 12 NULL 13 NULL 14 NULL 15 SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 0 Sort_scan 0 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 1 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 FLUSH STATUS; DELETE FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 0 Sort_scan 0 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 1 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i; a i NULL 16 NULL 17 NULL 18 DROP TABLE t2; # # index on field prefix: # CREATE TABLE t2(a INT, i CHAR(2), INDEX(i(1))); INSERT INTO t2 (i) SELECT i FROM t1; FLUSH STATUS; SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; a i NULL 11 NULL 12 NULL 13 NULL 14 NULL 15 SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 5 Sort_scan 1 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 17 FLUSH STATUS; DELETE FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 8 Sort_scan 1 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 5 Handler_read_rnd_next 17 SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i; a i NULL 16 NULL 17 NULL 18 DROP TABLE t2; # # constant inside ORDER BY list, should use filesort # on a small table # CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; FLUSH STATUS; SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; a b c d 10 10 10 NULL SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 1 Sort_scan 1 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 17 FLUSH STATUS; DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 1 Sort_scan 1 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 1 Handler_read_rnd_next 17 ## should be 5 (previous LIMIT) SELECT 1 - COUNT(*) FROM t2 WHERE b = 10; 1 - COUNT(*) 1 DROP TABLE t2; # # same test as above (constant inside ORDER BY list), but with # a larger table - should not use filesort # CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2; FLUSH STATUS; SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; a b c d 10 10 10 NULL 10 10 10 NULL 10 10 10 NULL 10 10 10 NULL 10 10 10 NULL SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 0 Sort_scan 0 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 1 Handler_read_key 0 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 FLUSH STATUS; DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 0 Sort_scan 0 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 1 Handler_read_key 0 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 ## should be 5 (previous LIMIT) SELECT 257 - COUNT(*) FROM t2 WHERE b = 10; 257 - COUNT(*) 5 DROP TABLE t2; # # as above + partial index, should use filesort # CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b(1),c)); INSERT INTO t2 SELECT i, i, i, i FROM t1; FLUSH STATUS; SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; a b c d 10 10 10 10 SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 1 Sort_scan 1 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 17 FLUSH STATUS; DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 1 Sort_scan 1 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 1 Handler_read_rnd_next 17 SELECT * FROM t2 WHERE b = 10 ORDER BY a, c; a b c d DROP TABLE t2; # # as above but index is without HA_READ_ORDER flag, should use filesort # CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b,c)) ENGINE=HEAP; INSERT INTO t2 SELECT i, i, i, i FROM t1; FLUSH STATUS; SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; a b c d 10 10 10 10 SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 1 Sort_scan 1 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 1 Handler_read_rnd_next 17 FLUSH STATUS; DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 1 Sort_scan 1 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 1 Handler_read_rnd_next 17 SELECT * FROM t2 WHERE b = 10 ORDER BY a, c; a b c d DROP TABLE t2; # # quick select is Index Merge, should use filesort # CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2)); INSERT INTO t2 (key1, key2) SELECT i, i FROM t1; FLUSH STATUS; SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; i key1 key2 NULL 10 10 NULL 11 11 NULL 12 12 NULL 13 13 SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 1 Sort_rows 4 Sort_scan 0 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 2 Handler_read_last 0 Handler_read_next 7 Handler_read_prev 0 Handler_read_rnd 4 Handler_read_rnd_next 0 EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; id select_type table type possible_keys key key_len ref rows filtered Extra x x x x x x x x x x Using sort_union(key1,key2); Using where; Using filesort Warnings: x x x FLUSH STATUS; DELETE FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 1 Sort_rows 4 Sort_scan 0 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 2 Handler_read_last 0 Handler_read_next 7 Handler_read_prev 0 Handler_read_rnd 8 Handler_read_rnd_next 0 SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; i key1 key2 EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; id select_type table type possible_keys key key_len ref rows filtered Extra x x x x x x x x x x Using sort_union(key1,key2); Using where; Using filesort Warnings: x x x DROP TABLE t2; # # reverse quick select, should not use filesort # CREATE TABLE t2(a INT, i INT PRIMARY KEY); INSERT INTO t2 (i) SELECT i FROM t1; FLUSH STATUS; SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5; a i NULL 18 NULL 17 NULL 16 NULL 15 NULL 14 SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 0 Sort_scan 0 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 1 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 4 Handler_read_rnd 0 Handler_read_rnd_next 0 FLUSH STATUS; DELETE FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5; SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 0 Sort_scan 0 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 1 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 4 Handler_read_rnd 0 Handler_read_rnd_next 0 SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i; a i NULL 11 NULL 12 NULL 13 DROP TABLE t2; # # mixed sorting direction, should use filesort # CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b)); INSERT INTO t2 SELECT i, i, i FROM t1; FLUSH STATUS; SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5; a b c 10 10 10 11 11 11 12 12 12 13 13 13 14 14 14 SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 5 Sort_scan 1 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 17 FLUSH STATUS; DELETE FROM t2 ORDER BY a, b DESC LIMIT 5; SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 16 Sort_scan 1 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 5 Handler_read_rnd_next 17 SELECT * FROM t2 ORDER BY a, b DESC; a b c 15 15 15 16 16 16 17 17 17 18 18 18 19 19 19 20 20 20 21 21 21 22 22 22 23 23 23 24 24 24 25 25 25 DROP TABLE t2; # # LIMIT with no WHERE and DESC direction, should not use filesort # CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b)); INSERT INTO t2 (a, b) SELECT i, i FROM t1; INSERT INTO t2 (a, b) SELECT t1.i, t1.i FROM t1, t1 x1, t1 x2; FLUSH STATUS; SELECT * FROM t2 ORDER BY a, b LIMIT 5; a b c 10 10 NULL 10 10 NULL 10 10 NULL 10 10 NULL 10 10 NULL SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 0 Sort_scan 0 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 1 Handler_read_key 0 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 FLUSH STATUS; SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5; a b c 25 25 NULL 25 25 NULL 25 25 NULL 25 25 NULL 25 25 NULL SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 0 Sort_scan 0 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 4 Handler_read_rnd 0 Handler_read_rnd_next 0 FLUSH STATUS; DELETE FROM t2 ORDER BY a DESC, b DESC LIMIT 5; SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 0 Sort_scan 0 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 4 Handler_read_rnd 0 Handler_read_rnd_next 0 SELECT * FROM t2 WHERE c = 10 ORDER BY a DESC, b DESC; a b c DROP TABLE t1, t2; # # Bug #36569: UPDATE ... WHERE ... ORDER BY... always does a filesort # even if not required # CREATE TABLE t1 (i INT); INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), (20),(21),(22),(23),(24),(25); CREATE TABLE t2(a INT, i INT PRIMARY KEY); INSERT INTO t2 (i) SELECT i FROM t1; FLUSH STATUS; SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; a i NULL 11 NULL 12 NULL 13 NULL 14 NULL 15 SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 0 Sort_scan 0 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 1 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 FLUSH STATUS; UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 0 Sort_scan 0 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 1 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 Handler_read_rnd 5 Handler_read_rnd_next 0 SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i; a i 10 11 10 12 10 13 10 14 10 15 NULL 16 NULL 17 NULL 18 DROP TABLE t2; # # index on field prefix: # CREATE TABLE t2(a INT, i CHAR(2), INDEX(i(1))); INSERT INTO t2 (i) SELECT i FROM t1; FLUSH STATUS; SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; a i NULL 11 NULL 12 NULL 13 NULL 14 NULL 15 SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 5 Sort_scan 1 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 17 FLUSH STATUS; UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 5 Sort_scan 1 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 5 Handler_read_rnd_next 17 SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i; a i 10 11 10 12 10 13 10 14 10 15 NULL 16 NULL 17 NULL 18 DROP TABLE t2; # # constant inside ORDER BY list, should use filesort # on a small table # CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; FLUSH STATUS; SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; a b c d 10 10 10 NULL SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 1 Sort_scan 1 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 17 FLUSH STATUS; UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5; SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 1 Sort_scan 1 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 1 Handler_read_rnd_next 17 ## should be 5 (previous LIMIT) SELECT COUNT(*) FROM t2 WHERE b = 10 AND d = 10 ORDER BY a, c; COUNT(*) 1 DROP TABLE t2; # # same test as above (constant inside ORDER BY list), but with # a larger table - should not use filesort # CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2; FLUSH STATUS; SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; a b c d 10 10 10 NULL 10 10 10 NULL 10 10 10 NULL 10 10 10 NULL 10 10 10 NULL SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 0 Sort_scan 0 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 1 Handler_read_key 0 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 FLUSH STATUS; UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5; SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 0 Sort_scan 0 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 1 Handler_read_key 0 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 Handler_read_rnd 5 Handler_read_rnd_next 0 ## should be 5 (previous LIMIT) SELECT COUNT(*) FROM t2 WHERE b = 10 AND d = 10 ORDER BY a, c; COUNT(*) 5 DROP TABLE t2; # # as above + partial index, should use filesort # CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b(1),c)); INSERT INTO t2 SELECT i, i, i, i FROM t1; FLUSH STATUS; SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; a b c d 10 10 10 10 SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 1 Sort_scan 1 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 17 FLUSH STATUS; UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5; SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 1 Sort_scan 1 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 1 Handler_read_rnd_next 17 SELECT * FROM t2 WHERE b = 10 ORDER BY a, c; a b c d 10 10 10 10 DROP TABLE t2; # # as above but index is without HA_READ_ORDER flag, should use filesort # CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b,c)) ENGINE=HEAP; INSERT INTO t2 SELECT i, i, i, i FROM t1; FLUSH STATUS; SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; a b c d 10 10 10 10 SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 1 Sort_scan 1 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 1 Handler_read_rnd_next 17 FLUSH STATUS; UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5; SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 1 Sort_scan 1 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 1 Handler_read_rnd_next 17 SELECT * FROM t2 WHERE b = 10 ORDER BY a, c; a b c d 10 10 10 10 DROP TABLE t2; # # quick select is Index Merge, should use filesort # CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2)); INSERT INTO t2 (key1, key2) SELECT i, i FROM t1; FLUSH STATUS; SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; i key1 key2 NULL 10 10 NULL 11 11 NULL 12 12 NULL 13 13 SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 1 Sort_rows 4 Sort_scan 0 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 2 Handler_read_last 0 Handler_read_next 7 Handler_read_prev 0 Handler_read_rnd 4 Handler_read_rnd_next 0 EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; id select_type table type possible_keys key key_len ref rows filtered Extra x x x x x x x x x x Using sort_union(key1,key2); Using where; Using filesort Warnings: x x x FLUSH STATUS; UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1; SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 1 Sort_rows 4 Sort_scan 0 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 2 Handler_read_last 0 Handler_read_next 7 Handler_read_prev 0 Handler_read_rnd 8 Handler_read_rnd_next 0 SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; i key1 key2 123 10 10 123 11 11 123 12 12 123 13 13 EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; id select_type table type possible_keys key key_len ref rows filtered Extra x x x x x x x x x x Using sort_union(key1,key2); Using where; Using filesort Warnings: x x x DROP TABLE t2; # # reverse quick select, should not use filesort # CREATE TABLE t2(a INT, i INT PRIMARY KEY); INSERT INTO t2 (i) SELECT i FROM t1; FLUSH STATUS; SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5; a i NULL 18 NULL 17 NULL 16 NULL 15 NULL 14 SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 0 Sort_scan 0 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 1 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 4 Handler_read_rnd 0 Handler_read_rnd_next 0 FLUSH STATUS; UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5; SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 0 Sort_scan 0 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 1 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 4 Handler_read_rnd 5 Handler_read_rnd_next 0 SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i; a i NULL 11 NULL 12 NULL 13 10 14 10 15 10 16 10 17 10 18 DROP TABLE t2; # # mixed sorting direction, should use filesort # CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b)); INSERT INTO t2 SELECT i, i, i FROM t1; FLUSH STATUS; SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5; a b c 10 10 10 11 11 11 12 12 12 13 13 13 14 14 14 SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 5 Sort_scan 1 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 17 FLUSH STATUS; UPDATE t2 SET c = 10 ORDER BY a, b DESC LIMIT 5; SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 5 Sort_scan 1 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 5 Handler_read_rnd_next 17 SELECT * FROM t2 WHERE c = 10 ORDER BY a, b DESC; a b c 10 10 10 11 11 10 12 12 10 13 13 10 14 14 10 DROP TABLE t2; # # LIMIT with no WHERE and DESC direction, should not use filesort # CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b)); INSERT INTO t2 (a, b) SELECT i, i FROM t1; INSERT INTO t2 (a, b) SELECT t1.i, t1.i FROM t1, t1 x1, t1 x2; FLUSH STATUS; SELECT * FROM t2 ORDER BY a, b LIMIT 5; a b c 10 10 NULL 10 10 NULL 10 10 NULL 10 10 NULL 10 10 NULL SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 0 Sort_scan 0 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 1 Handler_read_key 0 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 FLUSH STATUS; SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5; a b c 25 25 NULL 25 25 NULL 25 25 NULL 25 25 NULL 25 25 NULL SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 0 Sort_scan 0 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 4 Handler_read_rnd 0 Handler_read_rnd_next 0 FLUSH STATUS; UPDATE t2 SET c = 10 ORDER BY a DESC, b DESC LIMIT 5; SHOW SESSION STATUS LIKE 'Sort%'; Variable_name Value Sort_merge_passes 0 Sort_range 0 Sort_rows 0 Sort_scan 0 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 4 Handler_read_rnd 5 Handler_read_rnd_next 0 SELECT * FROM t2 WHERE c = 10 ORDER BY a DESC, b DESC; a b c 25 25 10 25 25 10 25 25 10 25 25 10 25 25 10 DROP TABLE t1, t2; # # Bug #53742: UPDATEs have no effect after applying patch for bug 36569 # CREATE TABLE t1 ( pk INT NOT NULL AUTO_INCREMENT, c1_idx CHAR(1) DEFAULT 'y', c2 INT, PRIMARY KEY (pk), INDEX c1_idx (c1_idx) ) ENGINE=InnoDB; INSERT INTO t1 VALUES (), (), (), (); SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; pk c1_idx c2 4 y NULL 3 y NULL UPDATE t1 SET c2 = 0 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; pk c1_idx c2 4 y 0 3 y 0 SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC; pk c1_idx c2 4 y 0 3 y 0 2 y NULL 1 y NULL DELETE FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC; pk c1_idx c2 2 y NULL 1 y NULL DROP TABLE t1;
Back to File Manager