An error occurred while parsing cardinality in MySQL.

An error occurred while parsing cardinality in MySQL.

Some time ago, early in the morning, I received an alarm warning that the number of php-fpm processes has exceeded the threshold. Eventually, an SQL statement is useless to the index, leading to slow execution of database queries, resulting in an increase in the number of php-fpm processes. Finally, the Cardinality is updated through the analyze table feed_comment_info_id_0000 command before the index can be used again.
The Troubleshooting process is as follows:
SQL statement:

select id from feed_comment_info_id_0000 where obj_id=101 and type=1;

Index information:

show index from feed_comment_info_id_0000+---------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |+---------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| feed_comment_info_id_0000 | 0 | PRIMARY | 1 | id   | A | 6216 | NULL | NULL |   | BTREE | | | feed_comment_info_id_0000 | 1 | obj_type | 1 | obj_id | A | 6216 | NULL | NULL |   | BTREE | | | feed_comment_info_id_0000 | 1 | obj_type | 2 | type  | A | 6216 | NULL | NULL | YES | BTREE | | | feed_comment_info_id_0000 | 1 | user_id | 1 | user_id | A | 6216 | NULL | NULL |   | BTREE | | +---------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+5 rows in set (0.00 sec)

When you look at the explain statement, it is found that the SQL statement uses the PRIMARY key PRIMARY instead of the obj_type index. View the Cardinality value of the index through show index, and find that this value is twice the actual data. The Cardinality value is not normal. Therefore, the Cardinality value is calculated again using the analyzea table command. After the command is executed, indexes can be used.

Cardinality
Explanation of the official document:
An estimate of the number of unique values in the index. this is updated by running analyze table or myisamchk-. cardinality is counted based on statistics stored as integers, so the value is not necessarily exact even for small tables. the higher the cardinality, the greater the chance that MySQL uses the index when doing
Summary:
1. It represents the estimated number of unique values in the index. For the myisam engine, this value is an accurate value. For the innodb engine, this value is an estimated value. Each time you execute show index, it may be different.
2. When creating an Index (except for the primary key), the Cardinality value of the MyISAM table is null, And the Cardinality value of the InnoDB table is about the number of rows;
3. The value size affects the selection of indexes.
4. When creating an Index, the Cardinality value of the MyISAM table is null, And the Cardinality value of the InnoDB table is roughly the number of rows.
5. You can use Analyze table to update a table or mysqlcheck-Aa to update the entire database.
6. You can view the value through show index.