| 1 |
create table ts(a int primary key, b int, c int, d int, index(b,c)); |
| 2 |
|
| 3 |
insert into ts |
| 4 |
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 |
| 5 |
,8,8,8),(9,9,9,9); |
| 6 |
|
| 7 |
ANALYZE TABLE ts; |
| 8 |
|
| 9 |
--let $i=0 |
| 10 |
while($i<2){ |
| 11 |
SELECT @@optimizer_switch LIKE '%index_condition_pushdown=on%'; |
| 12 |
explain select b,c,d from ts where b>=5 and b<8 and c=7 for update; |
| 13 |
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; |
| 14 |
begin; |
| 15 |
select b,c,d from ts where b>=5 and b<8 and c=7 for update; |
| 16 |
# hypergraph chooses a different plan, in particular doesn't use ICP which this test is all about |
| 17 |
--skip_if_hypergraph |
| 18 |
--sorted_result |
| 19 |
select INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks; |
| 20 |
commit; |
| 21 |
SET @@optimizer_switch='index_condition_pushdown=off'; |
| 22 |
--inc $i |
| 23 |
} |
| 24 |
|
| 25 |
DROP TABLE ts; |
| 26 |
|