Recently,I have reviwed the basic of SQL and found A question about index. My Working environment is as follows:

• os: Centos 7
• mysql: 5.7.39
• database: sakila
• table: customer AND My question is why Innodb uses idx_fk_store_id instead of the primary index when I use select count(customer_id) from customer

mysql> explain select count(customer_id) from customer;

AND Result:

The code to create this table is as follow:

CREATE TABLE `customer`(
`customer_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`store_id`   tinyint(3) unsigned NOT NULL,
PRIMARY KEY(`customer_id`),
KEY `idx_fk_store_id`(`store_id`)
) ENGINE=InnoDE AUTO_INCREMENT=600 DEFAULT CHARSET=utf8mb4

I've considered that it caused by MYSQL's Optimizer , even if it's hard to understand.

CodePudding user response：

The type: index in the EXPLAIN report indicates it is doing an index-scan. That is, reading every entry in the index idk_fk_store_id.

It can get the values of the primary key from a secondary index, so it can count them.

The alternative of using the primary key would be a table-scan, which reads every row of the table.

The primary key index in InnoDB is the clustered index. It stores all the columns of the table.

The secondary index stores only the values of store_id plus the primary key values of rows where a given store_id value occurs.

So it will be able to get the same answer by doing an index-scan, by reading fewer pages than doing the table-scan.