Description:
why mysql cann't using index to filter column after range condition ;
How to repeat:
# create table
use test;
drop table index_test;
create table index_test
(id bigint(20) unsigned NOT NULL AUTO_INCREMENT ,
col1 varchar(255),
col2 varchar(255),
col3 datetime,
col4 varchar(255),
primary key (id),
key (col1,col3,col2)
) engine=innodb charset=utf8;
# Insert data
for c1 in $(seq -w 500 )
do
col1="username"$c1
for c2 in $(seq -w 500 )
do
col2="othercol"$c2
col3=$(date +"%Y-%m-%d %H:%M:%S" -d "${c1} hour ago")
mysql -uroot -h127.0.0.1 test -e "insert into index_test values (null,'${col1}','${col2}','2010-06-03 00:00:00' - interval $c2 hour,'${col1}${col2}'); commit;"
done
done
###--- test.sql1
================================================
reset query cache;
show session status like 'Innodb_buffer_pool_read_requests';
select count(*) from index_test
where col1='username014'
and col3 >= '2010-05-01 00:00:00'
and col3 <= '2010-06-03 00:00:00' ;
show session status like 'Innodb_buffer_pool_read_requests';
## result : 500
## Innodb_buffer_pool_read_requests: 77
## well done , all search in index ;
###--- test.sql2
================================================
reset query cache;
show session status like 'Innodb_buffer_pool_read_requests';
select count(*) from index_test
where col1='username014'
and col3 >= '2010-05-01 00:00:00'
and col3 <= '2010-06-03 00:00:00'
####
and col2 = 'dsdlfkj' ;
show session status like 'Innodb_buffer_pool_read_requests';
## result : 0
## Innodb_buffer_pool_read_requests: 77
## well done , all search in index ;
###--- test.sql3
================================================
reset query cache;
show session status like 'Innodb_buffer_pool_read_requests';
select count(distinct col4 ) from index_test
where col1='username014'
and col3 >= '2010-05-01 00:00:00'
and col3 <= '2010-06-03 00:00:00';
show session status like 'Innodb_buffer_pool_read_requests';
## result : 500
## Innodb_buffer_pool_read_requests: 1550
###--- test.sql4
================================================
reset query cache;
show session status like 'Innodb_buffer_pool_read_requests';
select count(distinct col4 ) from index_test
where col1='username014'
and col3 >= '2010-05-01 00:00:00'
and col3 <= '2010-06-03 00:00:00'
####
and col2 = 'dsdlfkj' ;
show session status like 'Innodb_buffer_pool_read_requests';
## result : 0
## Innodb_buffer_pool_read_requests: 1542
## It's so Bad , col2 can't filter by index ;
## Mysql find all record (fileted col1 , col3 ) from table , then filter the col2='dsdlfkj'.
Suggested fix:
## As we think , test.sql4 should not to researh record from table , because result=0;
## That can be know while using index;
Description: why mysql cann't using index to filter column after range condition ; How to repeat: # create table use test; drop table index_test; create table index_test (id bigint(20) unsigned NOT NULL AUTO_INCREMENT , col1 varchar(255), col2 varchar(255), col3 datetime, col4 varchar(255), primary key (id), key (col1,col3,col2) ) engine=innodb charset=utf8; # Insert data for c1 in $(seq -w 500 ) do col1="username"$c1 for c2 in $(seq -w 500 ) do col2="othercol"$c2 col3=$(date +"%Y-%m-%d %H:%M:%S" -d "${c1} hour ago") mysql -uroot -h127.0.0.1 test -e "insert into index_test values (null,'${col1}','${col2}','2010-06-03 00:00:00' - interval $c2 hour,'${col1}${col2}'); commit;" done done ###--- test.sql1 ================================================ reset query cache; show session status like 'Innodb_buffer_pool_read_requests'; select count(*) from index_test where col1='username014' and col3 >= '2010-05-01 00:00:00' and col3 <= '2010-06-03 00:00:00' ; show session status like 'Innodb_buffer_pool_read_requests'; ## result : 500 ## Innodb_buffer_pool_read_requests: 77 ## well done , all search in index ; ###--- test.sql2 ================================================ reset query cache; show session status like 'Innodb_buffer_pool_read_requests'; select count(*) from index_test where col1='username014' and col3 >= '2010-05-01 00:00:00' and col3 <= '2010-06-03 00:00:00' #### and col2 = 'dsdlfkj' ; show session status like 'Innodb_buffer_pool_read_requests'; ## result : 0 ## Innodb_buffer_pool_read_requests: 77 ## well done , all search in index ; ###--- test.sql3 ================================================ reset query cache; show session status like 'Innodb_buffer_pool_read_requests'; select count(distinct col4 ) from index_test where col1='username014' and col3 >= '2010-05-01 00:00:00' and col3 <= '2010-06-03 00:00:00'; show session status like 'Innodb_buffer_pool_read_requests'; ## result : 500 ## Innodb_buffer_pool_read_requests: 1550 ###--- test.sql4 ================================================ reset query cache; show session status like 'Innodb_buffer_pool_read_requests'; select count(distinct col4 ) from index_test where col1='username014' and col3 >= '2010-05-01 00:00:00' and col3 <= '2010-06-03 00:00:00' #### and col2 = 'dsdlfkj' ; show session status like 'Innodb_buffer_pool_read_requests'; ## result : 0 ## Innodb_buffer_pool_read_requests: 1542 ## It's so Bad , col2 can't filter by index ; ## Mysql find all record (fileted col1 , col3 ) from table , then filter the col2='dsdlfkj'. Suggested fix: ## As we think , test.sql4 should not to researh record from table , because result=0; ## That can be know while using index;