Mysql index didn't take effect

Question

I'm working with mysql-5.7.16

Here's my table

+----------------+------------------+------+-----+-------------------+-----------------------------+
| Field          | Type             | Null | Key | Default           | Extra                       |
+----------------+------------------+------+-----+-------------------+-----------------------------+
| id             | int(11) unsigned | NO   | PRI | NULL              | auto_increment              |
| account_id     | varchar(45)      | YES  |     | NULL              |                             |
| account_name   | varchar(255)     | YES  |     | NULL              |                             |
| bm_id          | varchar(45)      | YES  |     | NULL              |                             |
| type           | tinyint(1)       | YES  |     | 0                 |                             |
| account_status | smallint(4)      | YES  |     | 0                 |                             |
| submitter      | varchar(128)     | YES  |     | NULL              |                             |
| submit_time    | timestamp        | YES  |     | CURRENT_TIMESTAMP |                             |
| status         | tinyint(1)       | YES  |     | 0                 |                             |
| create_time    | timestamp        | YES  |     | CURRENT_TIMESTAMP |                             |
| update_time    | timestamp        | YES  |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| is_delete      | tinyint(1)       | YES  |     | 0                 |                             |
| apply_id       | varchar(32)      | YES  |     | NULL              |                             |
| urgent         | tinyint(1)       | YES  |     | 0                 |                             |
+----------------+------------------+------+-----+-------------------+-----------------------------+


CREATE TABLE `ka_fb_account_spend_cap_handle` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `account_id` varchar(45) DEFAULT NULL,
  `account_name` varchar(255) DEFAULT NULL,
  `bm_id` varchar(45) DEFAULT NULL,
  `type` tinyint(1) DEFAULT '0',
  `account_status` smallint(4) DEFAULT '0',
  `submitter` varchar(128) DEFAULT NULL,
  `submit_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `status` tinyint(1) DEFAULT '0',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_delete` tinyint(1) DEFAULT '0',
  `apply_id` varchar(32) DEFAULT NULL,
  `urgent` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `condition` (`is_delete`,`submit_time`,`submitter`,`status`,`urgent`,`type`,`account_id`,`account_name`,`bm_id`,`account_status`,`apply_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

I want to use index condition and speed my query

EXPLAIN SELECT * 
FROM `ka_fb_account_spend_cap_handle` 
WHERE
(`ka_fb_account_spend_cap_handle`.`is_delete` = 0
AND `ka_fb_account_spend_cap_handle`.`status` IN (0, 1, 2, 3) 
AND `ka_fb_account_spend_cap_handle`.`submit_time` >= "1970-01-01 00:00:00" 
AND `ka_fb_account_spend_cap_handle`.`submit_time` <= "2021-03-26 11:54:00" 
AND `ka_fb_account_spend_cap_handle`.`submitter` LIKE  "%23123%" 
AND `ka_fb_account_spend_cap_handle`.`submitter` IN ("111@dd.com")
AND `ka_fb_account_spend_cap_handle`.`urgent` IN (0, 1) 
AND `ka_fb_account_spend_cap_handle`.`type` IN (0, 1, 2, 3) 
ORDER BY `ka_fb_account_spend_cap_handle`.`submit_time` DESC
LIMIT 10 OFFSET 10 

But index seems not taking effect:

+----+-------------+--------------------------------+------+---------------+-----------+---------+-------+--------+-------------+
| id | select_type | table                          | type | possible_keys | key       | key_len | ref   | rows   | Extra       |
+----+-------------+--------------------------------+------+---------------+-----------+---------+-------+--------+-------------+
|  1 | SIMPLE      | ka_fb_account_spend_cap_handle | all  | condition     | null      | null    | null   | 230361| Using where; Using filesort |
+----+-------------+--------------------------------+------+---------------+-----------+---------+-------+--------+-------------+

And if i change

 `ka_fb_account_spend_cap_handle`.`submit_time` >= "1970-01-01 00:00:00" 
AND `ka_fb_account_spend_cap_handle`.`submit_time` <= "2021-03-26 11:54:00" 

to

`ka_fb_account_spend_cap_handle`.`submit_time` BETWEEN "1970-01-01 00:00:00"  AND "2021-03-26 11:54:00" 

the result become

+----+-------------+--------------------------------+------+---------------+-----------+---------+-------+--------+-------------+
| id | select_type | table                          | type | possible_keys | key       | key_len | ref   | rows   | Extra       |
+----+-------------+--------------------------------+------+---------------+-----------+---------+-------+--------+-------------+
|  1 | SIMPLE      | ka_fb_account_spend_cap_handle | ref  | condition     | condition | 2       | const | 230361 | Using where |
+----+-------------+--------------------------------+------+---------------+-----------+---------+-------+--------+-------------+

Why the the condition changed and What should I do to optimize my query?

Thanks .

Answer 1

I'm surprised that the Optimizer did not pick

KEY `condition` (`is_delete`,`submit_time`, ...)

It would have used both of those columns both for the WHERE filtering and the ORDER BY.

Which of these do you need?

AND `ka_fb_account_spend_cap_handle`.`submitter` LIKE  "%23123%" 
AND `ka_fb_account_spend_cap_handle`.`submitter` IN ("111@dd.com")

(With both those clauses, you will get no rows in the resultset; the EXPLAIN possibly realizes this, making the EXPLAIN output useless.)

If it is the IN(one-item), I recommend adding

INDEX(is_delete, submitter,   -- first, in this order
      urgent, type, status,   -- next, in any order
      submit_time)            -- range last

Even if you sometimes put multiple items in that IN, this index may be useful.

Avoid "index hints"; such may "help today, but hurt tomorrow".

Re IN and optimization:

• col IN (one-item) is the same as col = one-item and the optimizer is smart enough to optimize them the same way

• col IN (1, 2) is harder to optimize that a single choice.

• If the IN list lists all possible values, then you are better off removing the AND IN (...). However, this may necessitate a different index. For example, if your query can thus be simplified to

    WHERE `is_delete` = 0
      AND `submit_time` >= "1970-01-01 00:00:00" 
      AND `submit_time` <= "2021-03-26 11:54:00" 
      AND `submitter` = "111@dd.com"
    ORDER BY ``submit_time` DESC
  

Then this is optimal:

INDEX(is_delete, submitter,   -- in either order
      submit_time)

In that case it will look at no more than 20 rows to handle LIMIT 10,10. Otherwise, it will probably look at lots of rows, sort them, skip 10, and deliver 10.