Optimizer chooses wrong index for LEFT JOIN query
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
||||
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.6 |
Triaged
|
Medium
|
Unassigned | |||
5.7 |
Triaged
|
Medium
|
Unassigned |
Bug Description
Under certain circumstances Optimizer uses wrong index for certain query.
mysql> flush tables; flush status;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT COUNT(DISTINCT `t1`.`id`) FROM `t1` INNER JOIN `t2` ON `t2`.`id` = `t1`.`f1` LEFT OUTER JOIN `t3` ON `t3`.`f1` = `t1`.`id` AND t3.f3 IS NULL AND `t3`.`f4` = 'Entry' LEFT OUTER JOIN `t3` `thumbnails_t3` ON `thumbnails_
+------
| COUNT(DISTINCT `t1`.`id`) |
+------
| 512 |
+------
1 row in set (13.73 sec)
mysql> show status like 'Handler_%';
+------
| Variable_name | Value |
+------
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 514 |
| Handler_read_last | 0 |
| Handler_read_next | 512512 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_
| Handler_update | 0 |
| Handler_write | 0 |
+------
18 rows in set (0.00 sec)
mysql> explain SELECT COUNT(DISTINCT `t1`.`id`) FROM `t1` INNER JOIN `t2` ON `t2`.`id` = `t1`.`f1` LEFT OUTER JOIN `t3` force index(f1) ON `t3`.`f1` = `t1`.`id` AND t3.f3 IS NULL AND `t3`.`f4` = 'Entry' LEFT OUTER JOIN `t3` `thumbnails_t3` ON `thumbnails_
+----+-
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-
| 1 | SIMPLE | t1 | NULL | ref | f3 | f3 | 4 | const | 511 | 10.00 | Using where |
| 1 | SIMPLE | t2 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 25 | 0.40 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | t3 | NULL | ref | f1 | f1 | 1032 | test.t1.id,const | 19 | 100.00 | Using where |
| 1 | SIMPLE | thumbnails_t3 | NULL | ref | f3 | f3 | 9 | test.t3.id | 3 | 100.00 | Using index |
+----+-
4 rows in set, 1 warning (0.00 sec)
mysql> flush tables; flush status; Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT COUNT(DISTINCT `t1`.`id`) FROM `t1` INNER JOIN `t2` ON `t2`.`id` = `t1`.`f1` LEFT OUTER JOIN `t3` force index(f1) ON `t3`.`f1` = `t1`.`id` AND t3.f3 IS NULL AND `t3`.`f4` = 'Entry' LEFT OUTER JOIN `t3` `thumbnails_t3` ON `thumbnails_
+------
| COUNT(DISTINCT `t1`.`id`) |
+------
| 512 |
+------
1 row in set (0.14 sec)
mysql> show status like 'Handler_%'; +------
| Variable_name | Value |
+------
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 514 |
| Handler_read_last | 0 |
| Handler_read_next | 512 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_
| Handler_update | 0 |
| Handler_write | 0 |
+------
18 rows in set (0.00 sec)
CREATE TABLE `t3` (
`id` bigint(20) NOT NULL,
`f1` bigint(20) DEFAULT NULL,
`f3` bigint(20) DEFAULT NULL,
`f4` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `f3` (`f3`),
KEY `f1` (`f1`,`f4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
Note that in the first case index f3 chosen while using f1 demonstrates better result.
Sometimes Optimizer chooses f3 index in optimizer_switch set to "use_index_
no longer affects: | percona-server/upstream-5.6 |
tags: | added: upstream |
To see optimizer_ switch= "use_index_ extensions= on/off" issue use larger file (#1), attached to the linked issue.