Solution to the problem that mysql index does not take 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 started querying 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 next day, the query slowed down again. I was a little curious whether there was new data written that caused the index not to be updated.

The above is the detailed content of Solution to the problem that mysql index does not take effect. For more information, please follow other related articles on the PHP Chinese website!