create table testicp(A INT,B INT,C varchar(20));
delimiter //
create procedure myproc3()
begin
declare num int;
set num=1;
while num <= 1000 do
insert into testicp values(num,num,'gaopeng');
set num=num+1;
end while;
end//
call myproc3() //
delimiter ;
alter table testicp add key(a,b);
explain select * from testicp where a=1 and b<10;
mysql> explain select * from testicp where a=1 and b<10;
+----+-------------+---------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+------+---------+------+------+-----------------------+
| 1 | SIMPLE | testicp | range | A | A | 10 | NULL | 1 | Using index condition |
+----+-------------+---------+-------+---------------+------+---------+------+------+-----------------------+
这里使用关键字Using index condition加以说明，他受参数
optimizer_switch='index_condition_pushdown=on'
影响，如果我们设置optimizer_switch='index_condition_pushdown=off'再来看一下
set optimizer_switch='index_condition_pushdown=off'
mysql> explain select * from testicp where a=1 and b<10;
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | testicp | range | A | A | 10 | NULL | 1 | Using where |
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)
可以看到这里变成了Using where，这代表没有使用icp。