Question 21248 in Mysql Question posted in
The official MySQL documentation can be found here.

Why is not primary index here? – Mysql

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:

type key Extra
index idx_fk_store_id Using index

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    Saying COUNT(x) is common mistake.

    • COUNT(*) counts the number of rows
    • COUNT(x) counts the number of rows where x is NOT NULL

    So, if you try SELECT COUNT(*) FROM customer, the Explain will again say that it is using that index. But for a different reason than Bill gave. This time the Optimizer’s logic goes this way:

    1. find the "smallest" index. That will happen to be the same store_id index.
    2. count the "rows" in that index. But no need to test whether customer_id IS NOT NULL.

    Another note: In MySQL, the PRIMARY KEY is required to be NOT NULL, so customer_id cannot be NULL. Also you declared it to be NOT NULL. Hence COUNT(customer_id) is necessarily the same as COUNT(*).

    TMI.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search