Why does MySQL take unique index over primary index?

Question

I have 2 very simple tables created as below:

create table company(
    company_id int auto_increment,
    company_no varchar(20),
    company_name varchar(20),
    primary key(company_id),
    unique index uni_company_no(company_no)
);
    
create table department(
    department_id int auto_increment,
    department_no varchar(20),
    department_name varchar(20),
    company_id int not null,
    primary key(department_id),
    unique index uni_department_no(department_no),
    index idx_company_id(company_id)
);

And if I query with the follwing SQL(I know it's an odd query), the result of EXPLAIN statenment shows that MySQL takes unique index(uni_company_no) instead of primary key:

explain select dept.* from department dept right join company com on dept.company_id = com.company_id;

EXPLAIN result

My questions are:

Why didn't MySQL use full table scan for table company?
Why didn't MySQL take primary key of table company if it decides to use index?

I'm very new to MySQL, so any input could be helpful. Thanks in advance.

1) Primary key is clustered index, hence its size is the table size. 2) Regular index includes primary key expression value hiddenly. 3) Unique index is more compact than the table size. 4) The data from `company` table is not used in the output. 5) There is no WHERE clause. #) Server uses `uni_company_no` index. — Akina, May 16 '22 at 06:27
Put everything needed to ask as text in your post, not just at a link & no images of what can be given as text without the text. Ask 1 question. [ask] [Help] [mre] — philipxy, May 16 '22 at 08:36
The query does not use columns from `company`, so it's cheaper to use the secondary index `uni_company_no` (that includes only two columns), instead of the primary index (that includes all the columns of the table). — The Impaler, May 16 '22 at 14:55

score 0 · Answer 1 · answered Apr 05 '23 at 08:32

I guess there maybe have cache in MySQL, I turn cache off and drop unique index and query two times. the answer is same (add unique index is faster).

mysql> # table structure
show create table big_tables;
 CREATE TABLE `big_tables` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) DEFAULT NULL,
  `age` bigint DEFAULT NULL,
  `data` longblob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10010001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
1 row in set (0.06 sec)

mysql> # explain query1
select * from big_tables where id >= (
    select id from big_tables limit 10000000, 1
) limit 0, 1;
+----------+------------+-----+------------+
| id       | name       | age | data       |
+----------+------------+-----+------------+
| 10000001 | R0HWlAyf7R |   0 | kHDTpsmtcg |
+----------+------------+-----+------------+
1 row in set (6.94 sec)

mysql> # explain query1
explain select * from big_tables where id >= (
    select id from big_tables limit 10000000, 1
) limit 0, 1;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | PRIMARY     | big_tables | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |   19290 |   100.00 | Using where |
|  2 | SUBQUERY    | big_tables | NULL       | index | NULL          | PRIMARY | 8       | NULL | 9750719 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
2 rows in set (2.93 sec)

mysql> # add unique index
alter table big_tables add unique index id(id) using btree;
Query OK, 0 rows affected (35.82 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> # query2
select * from big_tables where id >= (
    select id from big_tables limit 10000000, 1
) limit 0, 1;
+----------+------------+-----+------------+
| id       | name       | age | data       |
+----------+------------+-----+------------+
| 10000001 | R0HWlAyf7R |   0 | kHDTpsmtcg |
+----------+------------+-----+------------+
1 row in set (1.25 sec)

mysql> # explain query2
explain select * from big_tables where id >= (
    select id from big_tables limit 10000000, 1
) limit 0, 1;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | PRIMARY     | big_tables | NULL       | range | PRIMARY,id    | PRIMARY | 8       | NULL |   19290 |   100.00 | Using where |
|  2 | SUBQUERY    | big_tables | NULL       | index | NULL          | id      | 8       | NULL | 9750719 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
2 rows in set (1.45 sec)

mysql> # delete unique index
alter table big_tables drop index id;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> # query3
select * from big_tables where id >= (
    select id from big_tables limit 10000000, 1
) limit 0, 1;
+----------+------------+-----+------------+
| id       | name       | age | data       |
+----------+------------+-----+------------+
| 10000001 | R0HWlAyf7R |   0 | kHDTpsmtcg |
+----------+------------+-----+------------+
1 row in set (3.06 sec)

mysql> # query4
select * from big_tables where id >= (
    select id from big_tables limit 10000000, 1
) limit 0, 1;
+----------+------------+-----+------------+
| id       | name       | age | data       |
+----------+------------+-----+------------+
| 10000001 | R0HWlAyf7R |   0 | kHDTpsmtcg |
+----------+------------+-----+------------+
1 row in set (2.98 sec)

Why does MySQL take unique index over primary index?

1 Answers1