guoxin
/
test-example-projects
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
用于EagleEye3.0 规则集漏报和误报测试的示例项目，项目收集于github和gitee
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
3 Commits
1 Branch
0 Tags
722 MiB
 
 
 
 
 
 
Tag: Branch: Tree: master
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'master'
${ noResults }
test-example-projects/c++_projects/280w-mysql-8.0.18/mysql-test/r/skip_scan.result

1690 lines
77 KiB
Raw Permalink Blame History

CREATE TABLE t (a INT, b INT, c INT, d INT, e INT, PRIMARY KEY(a, b, c, d), KEY(b, d)) ENGINE=innodb;
# Since ANALYZE TABLE only reads a subset of the data, the statistics for
# table t depends on the row order. And since the INSERT INTO ... SELECT
# may be executed using different execution plans, we've added ORDER BY
# to ensure that we rows has the same order every time. If not, the
# estimated number of rows in EXPLAIN may change on different platforms.
ANALYZE TABLE t;
Table Op Msg_type Msg_text
test.t analyze status OK
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT b, d FROM t WHERE d < 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b b 8 NULL 833 100.00 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`d` AS `d` from `test`.`t` where (`test`.`t`.`d` < 2)
FLUSH STATUS;
SELECT b, d FROM t WHERE d < 2;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 1
Handler_read_key 11
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT b, d FROM t WHERE d < 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL index NULL b 8 NULL 2500 33.33 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`d` AS `d` from `test`.`t` where (`test`.`t`.`d` < 2)
FLUSH STATUS;
SELECT b, d FROM t WHERE d < 2;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 1
Handler_read_key 1
Handler_read_last 0
Handler_read_next 2500
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ b, d FROM t WHERE d < 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b b 8 NULL 833 100.00 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ `test`.`t`.`b` AS `b`,`test`.`t`.`d` AS `d` from `test`.`t` where (`test`.`t`.`d` < 2)
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ b, d FROM t WHERE d < 2;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 1
Handler_read_key 11
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT b, d FROM t WHERE d > 4;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b b 8 NULL 833 100.00 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`d` AS `d` from `test`.`t` where (`test`.`t`.`d` > 4)
FLUSH STATUS;
SELECT b, d FROM t WHERE d > 4;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 1
Handler_read_key 11
Handler_read_last 0
Handler_read_next 1500
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT b, d FROM t WHERE d > 4;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL index NULL b 8 NULL 2500 33.33 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`d` AS `d` from `test`.`t` where (`test`.`t`.`d` > 4)
FLUSH STATUS;
SELECT b, d FROM t WHERE d > 4;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 1
Handler_read_key 1
Handler_read_last 0
Handler_read_next 2500
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ b, d FROM t WHERE d > 4;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b b 8 NULL 833 100.00 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ `test`.`t`.`b` AS `b`,`test`.`t`.`d` AS `d` from `test`.`t` where (`test`.`t`.`d` > 4)
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ b, d FROM t WHERE d > 4;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 1
Handler_read_key 11
Handler_read_last 0
Handler_read_next 1500
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT a, b, c, d FROM t WHERE a = 5 AND d <= 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ref PRIMARY,b PRIMARY 4 const 250 33.33 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`d` <= 3))
FLUSH STATUS;
SELECT a, b, c, d FROM t WHERE a = 5 AND d <= 3;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT a, b, c, d FROM t WHERE a = 5 AND d <= 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ref PRIMARY PRIMARY 4 const 250 33.33 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`d` <= 3))
FLUSH STATUS;
SELECT a, b, c, d FROM t WHERE a = 5 AND d <= 3;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ a, b, c, d FROM t WHERE a = 5 AND d <= 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b PRIMARY 16 NULL 83 100.00 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`d` <= 3))
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ a, b, c, d FROM t WHERE a = 5 AND d <= 3;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 51
Handler_read_last 0
Handler_read_next 75
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT a, b, c, d FROM t WHERE a = 5 AND d >= 98;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ref PRIMARY,b PRIMARY 4 const 250 33.33 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`d` >= 98))
FLUSH STATUS;
SELECT a, b, c, d FROM t WHERE a = 5 AND d >= 98;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT a, b, c, d FROM t WHERE a = 5 AND d >= 98;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ref PRIMARY PRIMARY 4 const 250 33.33 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`d` >= 98))
FLUSH STATUS;
SELECT a, b, c, d FROM t WHERE a = 5 AND d >= 98;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ a, b, c, d FROM t WHERE a = 5 AND d >= 98;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b PRIMARY 16 NULL 83 100.00 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`d` >= 98))
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ a, b, c, d FROM t WHERE a = 5 AND d >= 98;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 51
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT a, b, c, d FROM t WHERE a IN (1, 5) AND d >= 98;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b PRIMARY 4 NULL 500 33.33 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` in (1,5)) and (`test`.`t`.`d` >= 98))
FLUSH STATUS;
SELECT a, b, c, d FROM t WHERE a IN (1, 5) AND d >= 98;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 2
Handler_read_last 0
Handler_read_next 500
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT a, b, c, d FROM t WHERE a IN (1, 5) AND d >= 98;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY PRIMARY 4 NULL 500 33.33 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` in (1,5)) and (`test`.`t`.`d` >= 98))
FLUSH STATUS;
SELECT a, b, c, d FROM t WHERE a IN (1, 5) AND d >= 98;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 2
Handler_read_last 0
Handler_read_next 500
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ a, b, c, d FROM t WHERE a IN (1, 5) AND d >= 98;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b PRIMARY 16 NULL 166 100.00 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` in (1,5)) and (`test`.`t`.`d` >= 98))
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ a, b, c, d FROM t WHERE a IN (1, 5) AND d >= 98;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 102
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT a, b, c, d FROM t WHERE a IN (1, 3, 5) AND d >= 98;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b PRIMARY 4 NULL 750 33.33 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` in (1,3,5)) and (`test`.`t`.`d` >= 98))
FLUSH STATUS;
SELECT a, b, c, d FROM t WHERE a IN (1, 3, 5) AND d >= 98;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 3
Handler_read_last 0
Handler_read_next 750
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT a, b, c, d FROM t WHERE a IN (1, 3, 5) AND d >= 98;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY PRIMARY 4 NULL 750 33.33 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` in (1,3,5)) and (`test`.`t`.`d` >= 98))
FLUSH STATUS;
SELECT a, b, c, d FROM t WHERE a IN (1, 3, 5) AND d >= 98;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 3
Handler_read_last 0
Handler_read_next 750
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ a, b, c, d FROM t WHERE a IN (1, 3, 5) AND d >= 98;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b PRIMARY 16 NULL 249 100.00 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` in (1,3,5)) and (`test`.`t`.`d` >= 98))
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ a, b, c, d FROM t WHERE a IN (1, 3, 5) AND d >= 98;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 153
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT a, b, c, d FROM t WHERE a IN (1, 5) AND b IN (1, 2) AND d >= 98;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b b 12 NULL 2 20.00 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` in (1,5)) and (`test`.`t`.`b` in (1,2)) and (`test`.`t`.`d` >= 98))
FLUSH STATUS;
SELECT a, b, c, d FROM t WHERE a IN (1, 5) AND b IN (1, 2) AND d >= 98;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 2
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT a, b, c, d FROM t WHERE a IN (1, 5) AND b IN (1, 2) AND d >= 98;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b b 12 NULL 2 20.00 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` in (1,5)) and (`test`.`t`.`b` in (1,2)) and (`test`.`t`.`d` >= 98))
FLUSH STATUS;
SELECT a, b, c, d FROM t WHERE a IN (1, 5) AND b IN (1, 2) AND d >= 98;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 2
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ a, b, c, d FROM t WHERE a IN (1, 5) AND b IN (1, 2) AND d >= 98;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b PRIMARY 16 NULL 66 100.00 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` in (1,5)) and (`test`.`t`.`b` in (1,2)) and (`test`.`t`.`d` >= 98))
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ a, b, c, d FROM t WHERE a IN (1, 5) AND b IN (1, 2) AND d >= 98;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 44
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT a, b, c, d FROM t WHERE a IN (1, 2, 3, 4, 5) AND b IN (1, 2, 3) AND d >= 98;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b b 12 NULL 3 50.00 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` in (1,2,3,4,5)) and (`test`.`t`.`b` in (1,2,3)) and (`test`.`t`.`d` >= 98))
FLUSH STATUS;
SELECT a, b, c, d FROM t WHERE a IN (1, 2, 3, 4, 5) AND b IN (1, 2, 3) AND d >= 98;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 3
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT a, b, c, d FROM t WHERE a IN (1, 2, 3, 4, 5) AND b IN (1, 2, 3) AND d >= 98;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b b 12 NULL 3 50.00 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` in (1,2,3,4,5)) and (`test`.`t`.`b` in (1,2,3)) and (`test`.`t`.`d` >= 98))
FLUSH STATUS;
SELECT a, b, c, d FROM t WHERE a IN (1, 2, 3, 4, 5) AND b IN (1, 2, 3) AND d >= 98;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 3
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ a, b, c, d FROM t WHERE a IN (1, 2, 3, 4, 5) AND b IN (1, 2, 3) AND d >= 98;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b PRIMARY 16 NULL 249 100.00 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` in (1,2,3,4,5)) and (`test`.`t`.`b` in (1,2,3)) and (`test`.`t`.`d` >= 98))
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ a, b, c, d FROM t WHERE a IN (1, 2, 3, 4, 5) AND b IN (1, 2, 3) AND d >= 98;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 165
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT a, b, c, d FROM t WHERE a = 5 AND b = 2 AND d >= 98;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b b 12 NULL 1 10.00 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`b` = 2) and (`test`.`t`.`a` = 5) and (`test`.`t`.`d` >= 98))
FLUSH STATUS;
SELECT a, b, c, d FROM t WHERE a = 5 AND b = 2 AND d >= 98;
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 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT a, b, c, d FROM t WHERE a = 5 AND b = 2 AND d >= 98;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b b 12 NULL 1 10.00 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`b` = 2) and (`test`.`t`.`a` = 5) and (`test`.`t`.`d` >= 98))
FLUSH STATUS;
SELECT a, b, c, d FROM t WHERE a = 5 AND b = 2 AND d >= 98;
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 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ a, b, c, d FROM t WHERE a = 5 AND b = 2 AND d >= 98;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b PRIMARY 16 NULL 16 100.00 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`b` = 2) and (`test`.`t`.`a` = 5) and (`test`.`t`.`d` >= 98))
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ a, b, c, d FROM t WHERE a = 5 AND b = 2 AND d >= 98;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 11
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT a+1, b, c, d FROM t WHERE a = 5 AND d < 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ref PRIMARY,b PRIMARY 4 const 250 33.33 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select (`test`.`t`.`a` + 1) AS `a+1`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT a+1, b, c, d FROM t WHERE a = 5 AND d < 3;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT a+1, b, c, d FROM t WHERE a = 5 AND d < 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ref PRIMARY PRIMARY 4 const 250 33.33 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select (`test`.`t`.`a` + 1) AS `a+1`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT a+1, b, c, d FROM t WHERE a = 5 AND d < 3;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ a+1, b, c, d FROM t WHERE a = 5 AND d < 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b PRIMARY 16 NULL 83 100.00 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ (`test`.`t`.`a` + 1) AS `a+1`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ a+1, b, c, d FROM t WHERE a = 5 AND d < 3;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 51
Handler_read_last 0
Handler_read_next 50
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT b, c, d FROM t WHERE a = 5 AND d < 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ref PRIMARY,b PRIMARY 4 const 250 33.33 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT b, c, d FROM t WHERE a = 5 AND d < 3;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT b, c, d FROM t WHERE a = 5 AND d < 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ref PRIMARY PRIMARY 4 const 250 33.33 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT b, c, d FROM t WHERE a = 5 AND d < 3;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ b, c, d FROM t WHERE a = 5 AND d < 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b PRIMARY 16 NULL 83 100.00 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ b, c, d FROM t WHERE a = 5 AND d < 3;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 51
Handler_read_last 0
Handler_read_next 50
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT a, b, c, d FROM t WHERE a = b AND d >= 98;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b b 8 NULL 833 10.00 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`b` = `test`.`t`.`a`) and (`test`.`t`.`d` >= 98))
FLUSH STATUS;
SELECT a, b, c, d FROM t WHERE a = b AND d >= 98;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 1
Handler_read_key 11
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT a, b, c, d FROM t WHERE a = b AND d >= 98;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL index NULL b 8 NULL 2500 33.33 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`b` = `test`.`t`.`a`) and (`test`.`t`.`d` >= 98))
FLUSH STATUS;
SELECT a, b, c, d FROM t WHERE a = b AND d >= 98;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 1
Handler_read_key 1
Handler_read_last 0
Handler_read_next 2500
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ a, b, c, d FROM t WHERE a = b AND d >= 98;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b b 8 NULL 833 10.00 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`b` = `test`.`t`.`a`) and (`test`.`t`.`d` >= 98))
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ a, b, c, d FROM t WHERE a = b AND d >= 98;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 1
Handler_read_key 11
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT b, c, d FROM t WHERE a = 5 AND c > 10 AND d < 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b PRIMARY 12 NULL 83 33.33 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` > 10) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT b, c, d FROM t WHERE a = 5 AND c > 10 AND d < 3;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 11
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT b, c, d FROM t WHERE a = 5 AND c > 10 AND d < 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ref PRIMARY PRIMARY 4 const 250 11.11 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` > 10) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT b, c, d FROM t WHERE a = 5 AND c > 10 AND d < 3;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ b, c, d FROM t WHERE a = 5 AND c > 10 AND d < 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b PRIMARY 12 NULL 83 33.33 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` > 10) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ b, c, d FROM t WHERE a = 5 AND c > 10 AND d < 3;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 11
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT b, c, d FROM t WHERE a = 5 AND c > 10 AND d < 3 ORDER BY d, c;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b PRIMARY 12 NULL 83 33.33 Using where; Using index for skip scan; Using filesort
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` > 10) and (`test`.`t`.`d` < 3)) order by `test`.`t`.`d`,`test`.`t`.`c`
FLUSH STATUS;
SELECT b, c, d FROM t WHERE a = 5 AND c > 10 AND d < 3 ORDER BY d, c;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 11
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT b, c, d FROM t WHERE a = 5 AND c > 10 AND d < 3 ORDER BY d, c;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ref PRIMARY PRIMARY 4 const 250 11.11 Using where; Using index; Using filesort
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` > 10) and (`test`.`t`.`d` < 3)) order by `test`.`t`.`d`,`test`.`t`.`c`
FLUSH STATUS;
SELECT b, c, d FROM t WHERE a = 5 AND c > 10 AND d < 3 ORDER BY d, c;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ b, c, d FROM t WHERE a = 5 AND c > 10 AND d < 3 ORDER BY d, c;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b PRIMARY 12 NULL 83 33.33 Using where; Using index for skip scan; Using filesort
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` > 10) and (`test`.`t`.`d` < 3)) order by `test`.`t`.`d`,`test`.`t`.`c`
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ b, c, d FROM t WHERE a = 5 AND c > 10 AND d < 3 ORDER BY d, c;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 11
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT b, c, d FROM t WHERE a = 5 AND c > 1 AND d < 3 ORDER BY d, c * -1;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b PRIMARY 12 NULL 83 33.33 Using where; Using index for skip scan; Using filesort
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` > 1) and (`test`.`t`.`d` < 3)) order by `test`.`t`.`d`,(`test`.`t`.`c` * -(1))
FLUSH STATUS;
SELECT b, c, d FROM t WHERE a = 5 AND c > 1 AND d < 3 ORDER BY d, c * -1;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 11
Handler_read_last 0
Handler_read_next 200
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT b, c, d FROM t WHERE a = 5 AND c > 1 AND d < 3 ORDER BY d, c * -1;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ref PRIMARY PRIMARY 4 const 250 11.11 Using where; Using index; Using filesort
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` > 1) and (`test`.`t`.`d` < 3)) order by `test`.`t`.`d`,(`test`.`t`.`c` * -(1))
FLUSH STATUS;
SELECT b, c, d FROM t WHERE a = 5 AND c > 1 AND d < 3 ORDER BY d, c * -1;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ b, c, d FROM t WHERE a = 5 AND c > 1 AND d < 3 ORDER BY d, c * -1;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b PRIMARY 12 NULL 83 33.33 Using where; Using index for skip scan; Using filesort
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` > 1) and (`test`.`t`.`d` < 3)) order by `test`.`t`.`d`,(`test`.`t`.`c` * -(1))
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ b, c, d FROM t WHERE a = 5 AND c > 1 AND d < 3 ORDER BY d, c * -1;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 11
Handler_read_last 0
Handler_read_next 200
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT e FROM t WHERE a = 5 AND d <= 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ref PRIMARY PRIMARY 4 const 250 33.33 Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`e` AS `e` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`d` <= 3))
FLUSH STATUS;
SELECT e FROM t WHERE a = 5 AND d <= 3;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT e FROM t WHERE a = 5 AND d <= 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ref PRIMARY PRIMARY 4 const 250 33.33 Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`e` AS `e` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`d` <= 3))
FLUSH STATUS;
SELECT e FROM t WHERE a = 5 AND d <= 3;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ e FROM t WHERE a = 5 AND d <= 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ref PRIMARY PRIMARY 4 const 250 33.33 Using where
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ `test`.`t`.`e` AS `e` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`d` <= 3))
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ e FROM t WHERE a = 5 AND d <= 3;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT b, c, d FROM t WHERE a = 5 AND c > 10 AND d < 3 ORDER BY d, c, e;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ref PRIMARY PRIMARY 4 const 250 11.11 Using where; Using filesort
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` > 10) and (`test`.`t`.`d` < 3)) order by `test`.`t`.`d`,`test`.`t`.`c`,`test`.`t`.`e`
FLUSH STATUS;
SELECT b, c, d FROM t WHERE a = 5 AND c > 10 AND d < 3 ORDER BY d, c, e;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT b, c, d FROM t WHERE a = 5 AND c > 10 AND d < 3 ORDER BY d, c, e;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ref PRIMARY PRIMARY 4 const 250 11.11 Using where; Using filesort
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` > 10) and (`test`.`t`.`d` < 3)) order by `test`.`t`.`d`,`test`.`t`.`c`,`test`.`t`.`e`
FLUSH STATUS;
SELECT b, c, d FROM t WHERE a = 5 AND c > 10 AND d < 3 ORDER BY d, c, e;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ b, c, d FROM t WHERE a = 5 AND c > 10 AND d < 3 ORDER BY d, c, e;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ref PRIMARY PRIMARY 4 const 250 11.11 Using where; Using filesort
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` > 10) and (`test`.`t`.`d` < 3)) order by `test`.`t`.`d`,`test`.`t`.`c`,`test`.`t`.`e`
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ b, c, d FROM t WHERE a = 5 AND c > 10 AND d < 3 ORDER BY d, c, e;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT count(a), count(b), count(c) FROM t WHERE a = 5 AND c > 10 AND d < 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b PRIMARY 12 NULL 83 33.33 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select count(`test`.`t`.`a`) AS `count(a)`,count(`test`.`t`.`b`) AS `count(b)`,count(`test`.`t`.`c`) AS `count(c)` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` > 10) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT count(a), count(b), count(c) FROM t WHERE a = 5 AND c > 10 AND d < 3;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 11
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT count(a), count(b), count(c) FROM t WHERE a = 5 AND c > 10 AND d < 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ref PRIMARY PRIMARY 4 const 250 11.11 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select count(`test`.`t`.`a`) AS `count(a)`,count(`test`.`t`.`b`) AS `count(b)`,count(`test`.`t`.`c`) AS `count(c)` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` > 10) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT count(a), count(b), count(c) FROM t WHERE a = 5 AND c > 10 AND d < 3;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ count(a), count(b), count(c) FROM t WHERE a = 5 AND c > 10 AND d < 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b PRIMARY 12 NULL 83 33.33 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ count(`test`.`t`.`a`) AS `count(a)`,count(`test`.`t`.`b`) AS `count(b)`,count(`test`.`t`.`c`) AS `count(c)` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` > 10) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ count(a), count(b), count(c) FROM t WHERE a = 5 AND c > 10 AND d < 3;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 11
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT min(a), max(b), min(c) FROM t WHERE a = 5 AND c > 10 AND d < 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b PRIMARY 12 NULL 83 33.33 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select min(`test`.`t`.`a`) AS `min(a)`,max(`test`.`t`.`b`) AS `max(b)`,min(`test`.`t`.`c`) AS `min(c)` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` > 10) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT min(a), max(b), min(c) FROM t WHERE a = 5 AND c > 10 AND d < 3;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 11
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT min(a), max(b), min(c) FROM t WHERE a = 5 AND c > 10 AND d < 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ref PRIMARY PRIMARY 4 const 250 11.11 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select min(`test`.`t`.`a`) AS `min(a)`,max(`test`.`t`.`b`) AS `max(b)`,min(`test`.`t`.`c`) AS `min(c)` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` > 10) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT min(a), max(b), min(c) FROM t WHERE a = 5 AND c > 10 AND d < 3;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ min(a), max(b), min(c) FROM t WHERE a = 5 AND c > 10 AND d < 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b PRIMARY 12 NULL 83 33.33 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ min(`test`.`t`.`a`) AS `min(a)`,max(`test`.`t`.`b`) AS `max(b)`,min(`test`.`t`.`c`) AS `min(c)` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` > 10) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ min(a), max(b), min(c) FROM t WHERE a = 5 AND c > 10 AND d < 3;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 11
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT sum(a), sum(b), sum(c) FROM t WHERE a = 5 AND c > 10 AND d < 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b PRIMARY 12 NULL 83 33.33 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select sum(`test`.`t`.`a`) AS `sum(a)`,sum(`test`.`t`.`b`) AS `sum(b)`,sum(`test`.`t`.`c`) AS `sum(c)` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` > 10) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT sum(a), sum(b), sum(c) FROM t WHERE a = 5 AND c > 10 AND d < 3;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 11
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT sum(a), sum(b), sum(c) FROM t WHERE a = 5 AND c > 10 AND d < 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ref PRIMARY PRIMARY 4 const 250 11.11 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select sum(`test`.`t`.`a`) AS `sum(a)`,sum(`test`.`t`.`b`) AS `sum(b)`,sum(`test`.`t`.`c`) AS `sum(c)` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` > 10) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT sum(a), sum(b), sum(c) FROM t WHERE a = 5 AND c > 10 AND d < 3;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ sum(a), avg(b), sum(c) FROM t WHERE a = 5 AND c > 10 AND d < 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b PRIMARY 12 NULL 83 33.33 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ sum(`test`.`t`.`a`) AS `sum(a)`,avg(`test`.`t`.`b`) AS `avg(b)`,sum(`test`.`t`.`c`) AS `sum(c)` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` > 10) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ sum(a), avg(b), sum(c) FROM t WHERE a = 5 AND c > 10 AND d < 3;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 11
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT count(*) FROM t WHERE d < 2;;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b b 8 NULL 833 100.00 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t` where (`test`.`t`.`d` < 2)
FLUSH STATUS;
SELECT count(*) FROM t WHERE d < 2;;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 1
Handler_read_key 11
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT count(*) FROM t WHERE d < 2;;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL index NULL b 8 NULL 2500 33.33 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t` where (`test`.`t`.`d` < 2)
FLUSH STATUS;
SELECT count(*) FROM t WHERE d < 2;;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 1
Handler_read_key 1
Handler_read_last 0
Handler_read_next 2500
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ count(*) FROM t WHERE d < 2;;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY,b b 8 NULL 833 100.00 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ count(0) AS `count(*)` from `test`.`t` where (`test`.`t`.`d` < 2)
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ count(*) FROM t WHERE d < 2;;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 1
Handler_read_key 11
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
Testing DESC index with skip scan.
ALTER TABLE t DROP PRIMARY KEY;
ALTER TABLE t DROP KEY b;
ALTER TABLE t ADD PRIMARY KEY(a DESC, b, c DESC, d);
ANALYZE TABLE t;
Table Op Msg_type Msg_text
test.t analyze status OK
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT b, c, d FROM t WHERE a = 5 AND c > 3 AND d < 3;;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY PRIMARY 12 NULL 83 33.33 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` > 3) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT b, c, d FROM t WHERE a = 5 AND c > 3 AND d < 3;;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 11
Handler_read_last 0
Handler_read_next 100
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT b, c, d FROM t WHERE a = 5 AND c > 3 AND d < 3;;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ref PRIMARY PRIMARY 4 const 250 11.11 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` > 3) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT b, c, d FROM t WHERE a = 5 AND c > 3 AND d < 3;;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ b, c, d FROM t WHERE a = 5 AND c > 3 AND d < 3;;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY PRIMARY 12 NULL 83 33.33 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` > 3) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ b, c, d FROM t WHERE a = 5 AND c > 3 AND d < 3;;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 11
Handler_read_last 0
Handler_read_next 100
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT b, c, d FROM t WHERE a = 5 AND c < 3 AND d < 3;;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY PRIMARY 12 NULL 83 33.33 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` < 3) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT b, c, d FROM t WHERE a = 5 AND c < 3 AND d < 3;;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 11
Handler_read_last 0
Handler_read_next 100
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT b, c, d FROM t WHERE a = 5 AND c < 3 AND d < 3;;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ref PRIMARY PRIMARY 4 const 250 11.11 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` < 3) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT b, c, d FROM t WHERE a = 5 AND c < 3 AND d < 3;;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ b, c, d FROM t WHERE a = 5 AND c < 3 AND d < 3;;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY PRIMARY 12 NULL 83 33.33 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` < 3) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ b, c, d FROM t WHERE a = 5 AND c < 3 AND d < 3;;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 11
Handler_read_last 0
Handler_read_next 100
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT b, c, d FROM t WHERE a = 5 AND c = 3 AND d < 3;;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY PRIMARY 12 NULL 50 33.33 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`c` = 3) and (`test`.`t`.`a` = 5) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT b, c, d FROM t WHERE a = 5 AND c = 3 AND d < 3;;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 11
Handler_read_last 0
Handler_read_next 50
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT b, c, d FROM t WHERE a = 5 AND c = 3 AND d < 3;;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ref PRIMARY PRIMARY 4 const 250 3.33 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`c` = 3) and (`test`.`t`.`a` = 5) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT b, c, d FROM t WHERE a = 5 AND c = 3 AND d < 3;;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ b, c, d FROM t WHERE a = 5 AND c = 3 AND d < 3;;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY PRIMARY 12 NULL 50 33.33 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`c` = 3) and (`test`.`t`.`a` = 5) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ b, c, d FROM t WHERE a = 5 AND c = 3 AND d < 3;;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 11
Handler_read_last 0
Handler_read_next 50
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT b, c, d FROM t WHERE a = 5 AND c BETWEEN 3 AND 5 AND d < 3;;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY PRIMARY 12 NULL 50 33.33 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` between 3 and 5) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT b, c, d FROM t WHERE a = 5 AND c BETWEEN 3 AND 5 AND d < 3;;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 11
Handler_read_last 0
Handler_read_next 150
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT b, c, d FROM t WHERE a = 5 AND c BETWEEN 3 AND 5 AND d < 3;;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ref PRIMARY PRIMARY 4 const 250 3.70 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` between 3 and 5) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT b, c, d FROM t WHERE a = 5 AND c BETWEEN 3 AND 5 AND d < 3;;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 1
Handler_read_last 0
Handler_read_next 250
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ b, c, d FROM t WHERE a = 5 AND c BETWEEN 3 AND 5 AND d < 3;;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY PRIMARY 12 NULL 50 33.33 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` between 3 and 5) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ b, c, d FROM t WHERE a = 5 AND c BETWEEN 3 AND 5 AND d < 3;;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 11
Handler_read_last 0
Handler_read_next 150
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT b, c, d FROM t WHERE c BETWEEN 3 AND 5 AND d < 3;;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY PRIMARY 12 NULL 500 33.33 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`c` between 3 and 5) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT b, c, d FROM t WHERE c BETWEEN 3 AND 5 AND d < 3;;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 1
Handler_read_key 101
Handler_read_last 0
Handler_read_next 1500
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT b, c, d FROM t WHERE c BETWEEN 3 AND 5 AND d < 3;;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL index NULL PRIMARY 16 NULL 2500 3.70 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`c` between 3 and 5) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT b, c, d FROM t WHERE c BETWEEN 3 AND 5 AND d < 3;;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 1
Handler_read_key 1
Handler_read_last 0
Handler_read_next 2500
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ b, c, d FROM t WHERE c BETWEEN 3 AND 5 AND d < 3;;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY PRIMARY 12 NULL 500 33.33 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`c` between 3 and 5) and (`test`.`t`.`d` < 3))
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ b, c, d FROM t WHERE c BETWEEN 3 AND 5 AND d < 3;;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 1
Handler_read_key 101
Handler_read_last 0
Handler_read_next 1500
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT b, c, d FROM t WHERE d < 3;;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL index PRIMARY PRIMARY 16 NULL 2500 33.33 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where (`test`.`t`.`d` < 3)
FLUSH STATUS;
SELECT b, c, d FROM t WHERE d < 3;;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 1
Handler_read_key 1
Handler_read_last 0
Handler_read_next 2500
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT b, c, d FROM t WHERE d < 3;;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL index NULL PRIMARY 16 NULL 2500 33.33 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where (`test`.`t`.`d` < 3)
FLUSH STATUS;
SELECT b, c, d FROM t WHERE d < 3;;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 1
Handler_read_key 1
Handler_read_last 0
Handler_read_next 2500
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ b, c, d FROM t WHERE d < 3;;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY PRIMARY 16 NULL 833 100.00 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where (`test`.`t`.`d` < 3)
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ b, c, d FROM t WHERE d < 3;;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 1
Handler_read_key 501
Handler_read_last 0
Handler_read_next 500
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT a, b, c, d FROM t WHERE a IN (1, 3, 5) AND d >= 5;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY PRIMARY 4 NULL 750 33.33 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` in (1,3,5)) and (`test`.`t`.`d` >= 5))
FLUSH STATUS;
SELECT a, b, c, d FROM t WHERE a IN (1, 3, 5) AND d >= 5;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 3
Handler_read_last 0
Handler_read_next 750
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT a, b, c, d FROM t WHERE a IN (1, 3, 5) AND d >= 5;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY PRIMARY 4 NULL 750 33.33 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` in (1,3,5)) and (`test`.`t`.`d` >= 5))
FLUSH STATUS;
SELECT a, b, c, d FROM t WHERE a IN (1, 3, 5) AND d >= 5;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 3
Handler_read_last 0
Handler_read_next 750
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ a, b, c, d FROM t WHERE a IN (1, 3, 5) AND d >= 5;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY PRIMARY 16 NULL 249 100.00 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` in (1,3,5)) and (`test`.`t`.`d` >= 5))
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ a, b, c, d FROM t WHERE a IN (1, 3, 5) AND d >= 5;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 153
Handler_read_last 0
Handler_read_next 450
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT a, b, c, d FROM t WHERE a IN (1, 5, 3) AND b IN (1, 2) AND d >= 5;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY PRIMARY 8 NULL 300 33.33 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` in (1,5,3)) and (`test`.`t`.`b` in (1,2)) and (`test`.`t`.`d` >= 5))
FLUSH STATUS;
SELECT a, b, c, d FROM t WHERE a IN (1, 5, 3) AND b IN (1, 2) AND d >= 5;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 6
Handler_read_last 0
Handler_read_next 300
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT a, b, c, d FROM t WHERE a IN (1, 5, 3) AND b IN (1, 2) AND d >= 5;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY PRIMARY 8 NULL 300 33.33 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` in (1,5,3)) and (`test`.`t`.`b` in (1,2)) and (`test`.`t`.`d` >= 5))
FLUSH STATUS;
SELECT a, b, c, d FROM t WHERE a IN (1, 5, 3) AND b IN (1, 2) AND d >= 5;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 6
Handler_read_last 0
Handler_read_next 300
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ a, b, c, d FROM t WHERE a IN (1, 5, 3) AND b in (1, 2) AND d >= 5;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range PRIMARY PRIMARY 16 NULL 99 100.00 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t`@`select#1`) */ `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` in (1,5,3)) and (`test`.`t`.`b` in (1,2)) and (`test`.`t`.`d` >= 5))
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t) */ a, b, c, d FROM t WHERE a IN (1, 5, 3) AND b in (1, 2) AND d >= 5;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 66
Handler_read_last 0
Handler_read_next 180
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = default;
DROP TABLE t;
Testing DESC index with skip scan and NULL range.
CREATE TABLE t1 (a INT, b INT, c INT, d INT, e INT);
INSERT INTO t1 VALUES
(NULL, 1, 1, 3, 4), (NULL, 2, 1, 4, 5),
(1, 2, 1, 3, 4), (2, 2, 1, 4, 5),
(5, 2, 3, 3, 4), (2, 2, 1, 4, 11),
(8, 2, 1, 3, 4), (7, 2, 1, 4, 9);
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
ALTER TABLE t1 ADD KEY k1(a DESC, b, c DESC, d, e DESC);
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT a, b, c, d FROM t1 WHERE (a IN (1, 5) or a IS NULL) AND b in (1, 2) AND d BETWEEN 2 AND 5;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL range k1 k1 10 NULL 34 11.11 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (((`test`.`t1`.`a` in (1,5)) or (`test`.`t1`.`a` is null)) and (`test`.`t1`.`b` in (1,2)) and (`test`.`t1`.`d` between 2 and 5))
FLUSH STATUS;
SELECT a, b, c, d FROM t1 WHERE (a IN (1, 5) or a IS NULL) AND b in (1, 2) AND d BETWEEN 2 AND 5;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 6
Handler_read_last 0
Handler_read_next 32
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT a, b, c, d FROM t1 WHERE (a IN (1, 5) or a IS NULL) AND b in (1, 2) AND d BETWEEN 2 AND 5;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL range k1 k1 10 NULL 34 11.11 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (((`test`.`t1`.`a` in (1,5)) or (`test`.`t1`.`a` is null)) and (`test`.`t1`.`b` in (1,2)) and (`test`.`t1`.`d` between 2 and 5))
FLUSH STATUS;
SELECT a, b, c, d FROM t1 WHERE (a IN (1, 5) or a IS NULL) AND b in (1, 2) AND d BETWEEN 2 AND 5;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 6
Handler_read_last 0
Handler_read_next 32
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t1) */ a, b, c, d FROM t1 WHERE (a IN (1, 5) or a IS NULL) AND b in (1, 2) AND d BETWEEN 2 AND 5;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL range k1 k1 20 NULL 34 100.00 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t1`@`select#1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (((`test`.`t1`.`a` in (1,5)) or (`test`.`t1`.`a` is null)) and (`test`.`t1`.`b` in (1,2)) and (`test`.`t1`.`d` between 2 and 5))
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t1) */ a, b, c, d FROM t1 WHERE (a IN (1, 5) or a IS NULL) AND b in (1, 2) AND d BETWEEN 2 AND 5;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 14
Handler_read_last 0
Handler_read_next 32
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
ALTER TABLE t1 DROP KEY k1;
ALTER TABLE t1 ADD KEY k1(a, b DESC, c, d DESC, e);
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
set optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT a, b, c, d FROM t1 WHERE (a IN (1, 5) or a IS NULL) AND b in (1, 2) AND d BETWEEN 2 AND 5;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL range k1 k1 10 NULL 34 11.11 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (((`test`.`t1`.`a` in (1,5)) or (`test`.`t1`.`a` is null)) and (`test`.`t1`.`b` in (1,2)) and (`test`.`t1`.`d` between 2 and 5))
FLUSH STATUS;
SELECT a, b, c, d FROM t1 WHERE (a IN (1, 5) or a IS NULL) AND b in (1, 2) AND d BETWEEN 2 AND 5;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 6
Handler_read_last 0
Handler_read_next 32
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
set optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT a, b, c, d FROM t1 WHERE (a IN (1, 5) or a IS NULL) AND b in (1, 2) AND d BETWEEN 2 AND 5;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL range k1 k1 10 NULL 34 11.11 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (((`test`.`t1`.`a` in (1,5)) or (`test`.`t1`.`a` is null)) and (`test`.`t1`.`b` in (1,2)) and (`test`.`t1`.`d` between 2 and 5))
FLUSH STATUS;
SELECT a, b, c, d FROM t1 WHERE (a IN (1, 5) or a IS NULL) AND b in (1, 2) AND d BETWEEN 2 AND 5;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 6
Handler_read_last 0
Handler_read_next 32
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.skip_scan, test.no_skip_scan]
EXPLAIN SELECT /*+ SKIP_SCAN(t1) */ a, b, c, d FROM t1 WHERE (a IN (1, 5) or a IS NULL) AND b in (1, 2) AND d BETWEEN 2 AND 5;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL range k1 k1 20 NULL 34 100.00 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t1`@`select#1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (((`test`.`t1`.`a` in (1,5)) or (`test`.`t1`.`a` is null)) and (`test`.`t1`.`b` in (1,2)) and (`test`.`t1`.`d` between 2 and 5))
FLUSH STATUS;
SELECT /*+ SKIP_SCAN(t1) */ a, b, c, d FROM t1 WHERE (a IN (1, 5) or a IS NULL) AND b in (1, 2) AND d BETWEEN 2 AND 5;
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 14
Handler_read_last 0
Handler_read_next 32
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
include/diff_tables.inc [test.hint_skip_scan, test.no_skip_scan]
set optimizer_switch = default;
DROP TABLE t1;
#
# Bug#28089360 WL#11322: SIG11 AT QEP_SHARED_OWNER::JOIN | SQL/SQL_OPT_EXEC_SHARED.H:458
#
CREATE TABLE t1 (f1 INT(11), f2 VARCHAR(1), KEY k1 (f2, f1));
Warnings:
Warning 1681 Integer display width is deprecated and will be removed in a future release.
INSERT INTO t1 VALUES (-682212662,'c'), (-1974921822,'C'), (1260604936,'9');
CREATE TABLE t2 (f1 INT(11));
Warnings:
Warning 1681 Integer display width is deprecated and will be removed in a future release.
INSERT INTO t2 VALUES (824388284), (1186821161);
CREATE VIEW v1 AS select f1, f2 from t1;
DELETE FROM t2 WHERE (f1, f1) IN (SELECT f1,f2 FROM v1 WHERE f1 >= 2);
DROP VIEW v1;
DROP TABLE t1, t2;
#
# Bug #29602393 ASSERTION `BITMAP_IS_SET(KEY_INFO->TABLE->READ_SET,
# KEY_INFO->KEY_PART.*FAILED
#
CREATE TABLE t1(f1 INT, f2 VARCHAR(64) NOT NULL, PRIMARY KEY (f1))
PARTITION BY RANGE(f1)
(
PARTITION p1 VALUES LESS THAN (3),
PARTITION p2 VALUES LESS THAN (maxvalue)
);
INSERT INTO t1 VALUES (1, 'abcde'), (2, 'abcde'), (3, 'abcde');
CREATE INDEX idx1 ON t1(f2);
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
EXPLAIN SELECT /*+ SKIP_SCAN(t1 idx1) */ count(*) FROM t1 WHERE f1 <= 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 p1,p2 range PRIMARY,idx1 idx1 262 NULL 1 100.00 Using where; Using index for skip scan
Warnings:
Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t1`@`select#1` `idx1`) */ count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`f1` <= 3)
SELECT /*+ SKIP_SCAN(t1 idx1) */ count(*) FROM t1 WHERE f1 <= 3;
count(*)
3
DROP TABLE t1;