| 1 |
create table ts(a int primary key, b int, c int, d int, index(b,c)); |
| 2 |
insert into ts |
| 3 |
values(1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4),(5,5,5,5),(6,6,6,6),(7,7,7,7),(8 |
| 4 |
,8,8,8),(9,9,9,9); |
| 5 |
ANALYZE TABLE ts; |
| 6 |
Table Op Msg_type Msg_text |
| 7 |
test.ts analyze status OK |
| 8 |
SELECT @@optimizer_switch LIKE '%index_condition_pushdown=on%'; |
| 9 |
@@optimizer_switch LIKE '%index_condition_pushdown=on%' |
| 10 |
1 |
| 11 |
explain select b,c,d from ts where b>=5 and b<8 and c=7 for update; |
| 12 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 13 |
1 SIMPLE ts NULL range b b 10 NULL 2 11.11 Using index condition |
| 14 |
Warnings: |
| 15 |
Note 1003 /* select#1 */ select `test`.`ts`.`b` AS `b`,`test`.`ts`.`c` AS `c`,`test`.`ts`.`d` AS `d` from `test`.`ts` where ((`test`.`ts`.`c` = 7) and (`test`.`ts`.`b` >= 5) and (`test`.`ts`.`b` < 8)) |
| 16 |
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; |
| 17 |
begin; |
| 18 |
select b,c,d from ts where b>=5 and b<8 and c=7 for update; |
| 19 |
b c d |
| 20 |
7 7 7 |
| 21 |
select INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks; |
| 22 |
INDEX_NAME LOCK_TYPE LOCK_MODE LOCK_STATUS LOCK_DATA |
| 23 |
NULL TABLE IX GRANTED NULL |
| 24 |
PRIMARY RECORD X,REC_NOT_GAP GRANTED 7 |
| 25 |
b RECORD X,REC_NOT_GAP GRANTED 7, 7, 7 |
| 26 |
commit; |
| 27 |
SET @@optimizer_switch='index_condition_pushdown=off'; |
| 28 |
SELECT @@optimizer_switch LIKE '%index_condition_pushdown=on%'; |
| 29 |
@@optimizer_switch LIKE '%index_condition_pushdown=on%' |
| 30 |
0 |
| 31 |
explain select b,c,d from ts where b>=5 and b<8 and c=7 for update; |
| 32 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 33 |
1 SIMPLE ts NULL range b b 10 NULL 2 11.11 Using where |
| 34 |
Warnings: |
| 35 |
Note 1003 /* select#1 */ select `test`.`ts`.`b` AS `b`,`test`.`ts`.`c` AS `c`,`test`.`ts`.`d` AS `d` from `test`.`ts` where ((`test`.`ts`.`c` = 7) and (`test`.`ts`.`b` >= 5) and (`test`.`ts`.`b` < 8)) |
| 36 |
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; |
| 37 |
begin; |
| 38 |
select b,c,d from ts where b>=5 and b<8 and c=7 for update; |
| 39 |
b c d |
| 40 |
7 7 7 |
| 41 |
select INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks; |
| 42 |
INDEX_NAME LOCK_TYPE LOCK_MODE LOCK_STATUS LOCK_DATA |
| 43 |
NULL TABLE IX GRANTED NULL |
| 44 |
PRIMARY RECORD X,REC_NOT_GAP GRANTED 7 |
| 45 |
b RECORD X,REC_NOT_GAP GRANTED 7, 7, 7 |
| 46 |
commit; |
| 47 |
SET @@optimizer_switch='index_condition_pushdown=off'; |
| 48 |
DROP TABLE ts; |