Solution to MySQL index not taking effect

The mysql used by the company for services has been very slow recently when querying. It often takes more than 10 seconds. I checked the execution plan of the query and found that the index did not take effect.

The storage engine uses InnoDB.

When I first queried in the main database, I was always curious about why the index did not take effect. After switching to the standby database, I found that the standby database was effective.

I started to think about whether there was a problem with the index, and then rebuilt the index and found that the efficiency was much higher.

Simply record the comparison.

mysql> explain select * from runinfo where status in (0, 2, 1, 3, 4, 7, 9, 10);
+----+-------------+---------+-------+---------------+------+---------+------+----------+-------------+
| id | select_type | table   | type  | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+---------+-------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | runinfo | All   | status_2      | NULL | NULL    | NULL |  2378055 | Using where |
+----+-------------+---------+-------+---------------+------+---------+------+----------+-------------+
row in set (0.00 sec)

The above is the execution plan of the main library.

Compare the execution plan of the standby database.

mysql> explain select * from runinfo where status in (0, 2, 1, 3, 4, 7, 9, 10);
+----+-------------+---------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | runinfo | range | status_2      | status_2 | 4       | NULL |  116 | Using where |
+----+-------------+---------+-------+---------------+----------+---------+------+------+-------------+
row in set (0.00 sec)

It can be seen that the standby database adapts to index status_2 during query.

After executing the following command, the problem is solved.

mysql> OPTIMIZE TABLE runinfo;
+------------------+----------+----------+-------------------------------------------------------------------+
| Table            | Op       | Msg_type | Msg_text                                                          |
+------------------+----------+----------+-------------------------------------------------------------------+
| schedule.runinfo | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| schedule.runinfo | optimize | status   | OK                                                                |
+------------------+----------+----------+-------------------------------------------------------------------+
rows in set (47.13 sec)

The above is the detailed content of Solution to MySQL index not taking effect. For more information, please follow other related articles on the PHP Chinese website!