Which index does MySQL count (*) choose?

Today, when querying a table row count, it is found that count (1) and COUNT (*) execution efficiency are the same. There's a difference between this and Oracle. Then look at the execution plan in two ways:

Mysql> Select COUNT (1) from customer;+----------+| Count (1) |+----------+|   150000 |+----------+1 row in Set (0.03 sec) mysql> flush tables; Query OK, 0 rows Affected (0.00 sec) mysql> Select COUNT (*) from customer;+----------+| COUNT (*) |+----------+|   150000 |+----------+1 row in Set (0.03 sec)

To view the execution plan:

Mysql> Explain select COUNT (1) from customer;+----+-------------+----------+-------+---------------+------------ ---+---------+------+--------+-------------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |+----+-------------+----------+-------+---------------+---------------+---------+------+--------+--------  -----+| 1 | Simple | Customer | Index | NULL | I_c_nationkey | 5 | NULL | 151191 | Using index |+----+-------------+----------+-------+---------------+---------------+---------+------+--------+-- -----------+1 Row in Set (0.00 sec) mysql> Explain select count (*) from customer;+----+-------------+----------+------ -+---------------+---------------+---------+------+--------+-------------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |+----+-------------+----------+-------+---------------+---------------+---------+------+--------+-------------+| 1 | Simple | Customer | Index | NULL | I_c_nationkey | 5 | NULL | 151191 | Using index |+----+-------------+----------+-------+---------------+---------------+---------+------+--------+-- -----------+1 Row in Set (0.00 sec) mysql> Show index from customer;+----------+------------+---------------+-------- ------+-------------+-----------+-------------+----------+--------+------+------------+---------+-------------- -+| Table | Non_unique | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+----------+------------+---------------+--------------+-------------+-----------+-------------+ ----------+--------+------+------------+---------+---------------+|          Customer | 0 |            PRIMARY | 1 | C_custkey |      A |     150525 | NULL |      NULL | |         BTREE |               | ||          Customer | 1 |            I_c_nationkey | 1 | C_nationKey |          A |     47 | NULL | NULL | YES |         BTREE |               | |+----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--- -----+------+------------+---------+---------------+2 rows in Set (0.08 sec)

It is found that either count (1) or COUNT (*) is walking I_c_nationkey this index. Usually when we retrieve the data is the primary key index efficiency is high, then we force the primary key index to see:

Mysql> Select COUNT (*) from the Customer Force index (PRIMARY); +----------+| COUNT (*) |+----------+|   150000 |+----------+1 row in Set (0.68 sec) mysql> Explain select count (*) from Customer Force index (PRIMARY), +----+---- ---------+----------+-------+---------------+---------+---------+------+--------+-------------+| ID | Select_type | Table    | type  | possible_keys | key     | key_len | ref  | rows   | Extra       |+----+-------------+----------+-------+---------------+---------+---------+------+--------+--------- ----+|  1 | Simple      | customer | index | NULL          | PRIMARY | 4       | NULL | 150525 | Using index |+----+-------------+----------+-------+---------------+---------+---------+------+--------+-------- -----+1 Row in Set (0.00 sec)

You can see the index of the primary key when the efficiency is poor. So what is it for?
Usually when we retrieve a column, basically equivalent or range query, then the index cardinality of the index is necessarily highly efficient. However, when you do count (*), you do not retrieve a specific row or range. Then select an index pair with a small cardinality
Count operations are more efficient. When you do the count operation, MySQL iterates through each leaf node, so the smaller the cardinality, the higher the efficiency. MySQL non-clustered index leaf node holds the primary key ID, so it needs to be retrieved two times index. But this is relative to traversing the primary key index. Timely retrieval two times index efficiency is also faster than simply retrieving the primary key index.
Then take a table as proof:

Mysql> Explain select COUNT (*) from lineitem;+----+-------------+----------+-------+---------------+------------ --+---------+------+---------+-------------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |+----+-------------+----------+-------+---------------+--------------+---------+------+---------+--------  -----+| 1 | Simple | LineItem | Index | NULL | I_l_shipdate | 4 | NULL | 6008735 | Using index |+----+-------------+----------+-------+---------------+--------------+---------+------+---------+-- -----------+1 Row in Set (0.00 sec) mysql> Show index from lineitem;+----------+------------+-----------------------+ --------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---- -----------+| Table | Non_unique | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_commENT |+----------+------------+-----------------------+--------------+---------------+-----------+-------------+- ---------+--------+------+------------+---------+---------------+|          LineItem | 0 |            PRIMARY | 1 | L_orderkey |     A |     2997339 | NULL |      NULL | |         BTREE |               | ||          LineItem | 0 |            PRIMARY | 2 | L_linenumber |     A |     5994679 | NULL |      NULL | |         BTREE |               | ||          LineItem | 1 |            I_l_shipdate | 1 | L_shipdate |        A |     5208 | NULL | NULL | YES |         BTREE |               | ||          LineItem | 1 |            I_l_suppkey_partkey | 1 | L_partkey |      A |     428191 | NULL | NULL | YES |         BTREE |               | ||          LineItem | 1 |            I_l_suppkey_partkey | 2 | L_suppkey |     A |     1998226 | NULL | NULL | YES | BTREE |               | ||          LineItem | 1 |            I_l_partkey | 1 | L_partkey |      A |     461129 | NULL | NULL | YES |         BTREE |               | ||          LineItem | 1 |            I_l_suppkey | 1 | L_suppkey |       A |     19213 | NULL | NULL | YES |         BTREE |               | ||          LineItem | 1 |            I_l_receiptdate | 1 | L_receiptdate |          A |     17 | NULL | NULL | YES |         BTREE |               | ||          LineItem | 1 |            I_l_orderkey | 1 | L_orderkey |     A |     2997339 | NULL |      NULL | |         BTREE |               | ||          LineItem | 1 |            i_l_orderkey_quantity | 1 | L_orderkey |     A |     1998226 | NULL |      NULL | |         BTREE |               | ||          LineItem | 1 |            i_l_orderkey_quantity | 2 | l_quantity |     A | 5994679 | NULL | NULL | YES |         BTREE |               | ||          LineItem | 1 |            I_l_commitdate | 1 | L_commitdate |        A |     7836 | NULL | NULL | YES |         BTREE |               | |+----------+------------+-----------------------+--------------+---------------+-----------+-------------+---- ------+--------+------+------------+---------+---------------+12 rows in Set (0.96 sec)

Here a look at L_shipdate is not the base of the smallest ah, but this statistic information is not accurate. Let's look at it in SQL.

Mysql> Select COUNT (Distinct (l_shipdate)) from lineitem;+-----------------------------+| COUNT (Distinct (l_shipdate)) |+-----------------------------+|                        2526 |+-----------------------------+1 row in Set (0.01 sec)

What about the columns that are smaller than him?

Mysql> Select COUNT (Distinct (l_receiptdate)) from lineitem;+--------------------------------+| COUNT (Distinct (l_receiptdate)) |+--------------------------------+|                           2554 |+--------------------------------+1 row in Set (0.01 sec)

Other will not look, here again that MySQL chose the base small index.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Which index does MySQL count (*) choose?