Why select id using other index?

Question

I am using mysql8.0.27, here is my table:

CREATE TABLE `test` (
  `id` int NOT NULL,
  `number` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_number` (`number`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

why "select id from test;" using idx_number:

explain SELECT id FROM `test`;

Answer 1

You can think that select id from test doesn't use any index, because you don't have a 'where' clause and just select all the items in the table. It's a 'traverse'(or 'scan') action on the table.

However, it indeed uses the idx_number index(aka: second index) to find all the id in the table, because idx_number:

1. has all the primary id values;
2. is small than the clustered index(aka: the table);

So mysql choose to use idx_number index rather than the original table, traversing on the former is much faster than the latter.

type in explain output really indicates these difference:

• all: traverse on the original table;
• index: traverse on the secondary index, but it's also a traverse;
• range: this is really what we thought 'using a index';

As you can see in your question, select id from test uses index type(not range).