1. When testing a query according to the time range, although the index is added, it is found that the index cannot be used. You can use this to force the use of the index
The test process is to create the following table and create a joint index
create table delay_delete_users(
id int auto_increment,
email_id int not null default 0 comment "email table id",
email varchar(50) not null default "",
entid int not null default 0 ,
default_domain varchar(50) not null default "",
delete_time timestamp comment ,
clear tinyint not null default 0 comment,
primary key (id),
key email_entid(email,entid),
key delete_time(delete_time,clear)
)engine innodb;
Insert test data and perform explain query
insert into `delay_delete_users` (email,entid,default_domain,delete_time)value("shihan2",23684,"appdev.sinanet.com","2019-12-10 15:49:16");
insert into `delay_delete_users` (email,entid,default_domain,delete_time,clear)value("shihan2",23684,"appdev.sinanet.com","2019-12-10 15:49:16",1);
insert into `delay_delete_users` (email,entid,default_domain,delete_time,clear)value("shihan2",23684,"appdev.sinanet.com","2019-12-12 15:49:16",1);
explain select * from delay_delete_users where delete_time<'2019-12-12' and clear=0; The index is not used, or a full table scan is performed to see the number of rows scanned
+----+-------------+--------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | delay_delete_users | NULL | ALL | delete_time | NULL | NULL | NULL | 7 | 14.29 | Using where |
explain select * from delay_delete_users force index(delete_time) where delete_time<'2019-12-12' and clear=0; index is used
+----+-------------+--------------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | delay_delete_users | NULL | range | delete_time | delete_time | 4 | NULL | 3 | 14.29 | Using index condition |