Efficiently query on first two digits of indexed int column in MySQL-CodePudding

I have a table (MySQL 8.0.26, InnoDB) containing an indexed column of MEDIUMINTs that denote the date a record was created:

date_created MEDIUMINT NOT NULL
INDEX idx_created (date_created)

E.g., the entry "210516" denotes 2021-05-16.

Are the following queries roughly equally efficient in utilizing the index?

WHERE 210000<=date_created AND date_created<220000,

WHERE date_created DIV 10000 = 21,

WHERE date_created LIKE '21%', and

WHERE LEFT(date_created, 2) = '21'

I am currently using WHERE date_created DIV 10000 = 21 in my code but wonder if I should alter all queries to make them more efficient.

Thanks a lot in advance.

CodePudding user response：

Look at the type column in EXPLAIN. If it says "ALL" it means it must do a table-scan of all the rows, evaluating the condition expression for each row. This is not using the index.

mysql> explain select * from mytable where 21000<=date_created and date_created < 22000;
 ---- ------------- --------- ------------ ------- --------------- -------------- --------- ------ ------ ---------- ----------------------- 
| id | select_type | table   | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
 ---- ------------- --------- ------------ ------- --------------- -------------- --------- ------ ------ ---------- ----------------------- 
|  1 | SIMPLE      | mytable | NULL       | range | date_created  | date_created | 4       | NULL |    1 |   100.00 | Using index condition |
 ---- ------------- --------- ------------ ------- --------------- -------------- --------- ------ ------ ---------- ----------------------- 

mysql> explain select * from mytable where date_created like '21%';
 ---- ------------- --------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
 ---- ------------- --------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
|  1 | SIMPLE      | mytable | NULL       | ALL  | date_created  | NULL | NULL    | NULL | 8192 |    11.11 | Using where |
 ---- ------------- --------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 

mysql> explain select * from mytable where date_created div 10000 = 21;
 ---- ------------- --------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
 ---- ------------- --------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
|  1 | SIMPLE      | mytable | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 8192 |   100.00 | Using where |
 ---- ------------- --------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 

mysql> explain select * from mytable where left(date_created, 2) = '21';
 ---- ------------- --------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
 ---- ------------- --------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
|  1 | SIMPLE      | mytable | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 8192 |   100.00 | Using where |
 ---- ------------- --------- ------------ ------ --------------- ------ --------- ------ ------ ---------- -------------

MySQL 8.0 supports expression indexes, which helps a couple of the cases:

mysql> alter table mytable add index expr1 ((left(date_created, 2)));

mysql> explain select * from mytable where left(date_created, 2) = '21';
 ---- ------------- --------- ------------ ------ --------------- ------- --------- ------- ------ ---------- ------- 
| id | select_type | table   | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
 ---- ------------- --------- ------------ ------ --------------- ------- --------- ------- ------ ---------- ------- 
|  1 | SIMPLE      | mytable | NULL       | ref  | expr1         | expr1 | 11      | const | 1402 |   100.00 | NULL  |
 ---- ------------- --------- ------------ ------ --------------- ------- --------- ------- ------ ---------- ------- 

mysql> alter table mytable add index expr2 ((date_created DIV 10000));

mysql> explain select * from mytable where date_created div 10000 = 21;
 ---- ------------- --------- ------------ ------ --------------- ------- --------- ------- ------ ---------- ------- 
| id | select_type | table   | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
 ---- ------------- --------- ------------ ------ --------------- ------- --------- ------- ------ ---------- ------- 
|  1 | SIMPLE      | mytable | NULL       | ref  | expr2         | expr2 | 5       | const | 1402 |   100.00 | NULL  |
 ---- ------------- --------- ------------ ------ --------------- ------- --------- ------- ------ ---------- -------

But expression indexes won't help the LIKE '21%' search, because you'd have to hard-code the value '21%' in the expression for the index definition. You could use that index to search for that value only, not for the value of a different year.