Optimizer chooses wrong index for LEFT JOIN query

[Sveta Smirnova]

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_t3`.`f3` = `t3`.`id` WHERE `t1`.`f2` IS NULL AND `t1`.`f3` = 85260 AND (t1.f3 = 85260 AND t2.f2 IS NULL);
+---------------------------+
| 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_external_lock | 8 |
| 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_read_rnd_next | 26 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| 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_t3`.`f3` = `t3`.`id` WHERE `t1`.`f2` IS NULL AND `t1`.`f3` = 85260 AND (t1.f3 = 85260 AND t2.f2 IS NULL);
+----+-------------+---------------+------------+------+---------------+------+---------+------------------+------+----------+----------------------------------------------------+
| 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_t3`.`f3` = `t3`.`id` WHERE `t1`.`f2` IS NULL AND `t1`.`f3` = 85260 AND (t1.f3 = 85260 AND t2.f2 IS NULL);
+---------------------------+
| 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_external_lock | 8 |
| 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_read_rnd_next | 26 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| 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_extensions=off", but this is more likely repeatable with larger tables.

[Sveta Smirnova (svetasmirnova)]

To see optimizer_switch="use_index_extensions=on/off" issue use larger file (#1), attached to the linked issue.

[Sveta Smirnova (svetasmirnova)]

Server should be started with option innodb-file-format=barracuda

[Valerii Kravchuk (valerii-kravchuk)]

This is easy to confirm:

openxs@ao756:/tmp$ mysql -uroot -proot test
Warning: Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 5.6.24-72.2 Percona Server (GPL), Release 72.2, Revision 8d0f85b

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@innodb_file_format;
+----------------------+
| @@innodb_file_format |
+----------------------+
| Barracuda |
+----------------------+
1 row 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` ON `t3`.`f1` = `t1`.`id` AND t3.f3 IS NULL AND `t3`.`f4` = 'Entry' LEFT OUTER JOIN `t3` `thumbnails_t3` ON `thumbnails_t3`.`f3` = `t3`.`id` WHERE `t1`.`f2` IS NULL AND `t1`.`f3` = 85260 AND (t1.f3 = 85260 AND t2.f2 IS NULL);
+----+-------------+---------------+--------+---------------+---------+---------+------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+--------+---------------+---------+---------+------------+------+-------------+
| 1 | SIMPLE | t1 | ref | f3 | f3 | 4 | const | 511 | Using where |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 8 | test.t1.f1 | 1 | Using where |
| 1 | SIMPLE | t3 | ref | f3,f1 | f3 | 9 | const | 1 | Using where |
| 1 | SIMPLE | thumbnails_t3 | ref | f3 | f3 | 9 | test.t3.id | 1 | Using index |
+----+-------------+---------------+--------+---------------+---------+---------+------------+------+-------------+
4 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_t3`.`f3` = `t3`.`id` WHERE `t1`.`f2` IS NULL AND `t1`.`f3` = 85260 AND (t1.f3 = 85260 AND t2.f2 IS NULL);
+----+-------------+---------------+--------+---------------+---------+---------+------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+--------+---------------+---------+---------+------------------+------+-------------+
| 1 | SIMPLE | t1 | ref | f3 | f3 | 4 | const | 511 | Using where |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMAR...

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1631