# Test case - no particular variables were used mysql [localhost] {msandbox} ((none)) > status ; -------------- /opt/percona_server/5.7.23-23/bin/mysql Ver 14.14 Distrib 5.7.23-23, for Linux (x86_64) using 6.2 Connection id: 4 Current database: Current user: msandbox@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.23-23 Percona Server (GPL), Release 23, Revision 500fcf5 Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql_sandbox4012.sock Uptime: 10 min 46 sec Threads: 1 Questions: 346 Slow queries: 0 Opens: 126 Flush tables: 1 Open tables: 109 Queries per second avg: 0.535 -------------- $ /opt/percona_server/5.7.23-23/bin/mysql -u msandbox -pmsandbox --socket=/tmp/mysql_sandbox4012.sock < case_237160.sql $ /opt/percona_server/5.7.23-23/bin/mysql -u msandbox -pmsandbox --socket=/tmp/mysql_sandbox4012.sock test mysql> analyze table t1, t2, t3, t4, t5, t6, t7, t8, t9, t10 ; +----------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------+---------+----------+----------+ | test.t1 | analyze | status | OK | | test.t2 | analyze | status | OK | | test.t3 | analyze | status | OK | | test.t4 | analyze | status | OK | | test.t5 | analyze | status | OK | | test.t6 | analyze | status | OK | | test.t7 | analyze | status | OK | | test.t8 | analyze | status | OK | | test.t9 | analyze | status | OK | | test.t10 | analyze | status | OK | +----------+---------+----------+----------+ 10 rows in set (0.05 sec) # Query SELECT * FROM t1 INNER JOIN t2 ON t2.c1 = t1.c1 INNER JOIN t3 ON t3.c1 = t2.c1 AND t3.c2 = t2.c2 INNER JOIN t4 -- force index (key2) ON t4.c2 = t3.c1 AND t4.c3 = t3.c2 AND t4.c4 = t3.c3 INNER JOIN t5 ON t5.c1 = t4.c1 INNER JOIN t6 ON t6.c1 = t4.c5 INNER JOIN t7 ON t7.c2 = t4.c1 INNER JOIN t8 ON t8.c2 = t7.c1 WHERE t1.c1 IN (SELECT DISTINCT c3 FROM t9 WHERE t9.c1 = 13900180 AND t9.c3 = Ifnull(NULL, t9.c3)) AND t4.c1 IN (SELECT c3 FROM t10 WHERE c2 = 13900180); # Wrong Execution Plan - In case following plan is not obtained execute analyze table command again and check the plan. mysql> explain -> SELECT * -> FROM t1 -> INNER JOIN t2 -> ON t2.c1 = t1.c1 -> INNER JOIN t3 -> ON t3.c1 = t2.c1 -> AND t3.c2 = t2.c2 -> INNER JOIN t4 -> -- force index (key2) -> ON t4.c2 = t3.c1 -> AND t4.c3 = t3.c2 -> AND t4.c4 = t3.c3 -> INNER JOIN t5 -> ON t5.c1 = t4.c1 -> INNER JOIN t6 -> ON t6.c1 = t4.c5 -> INNER JOIN t7 -> ON t7.c2 = t4.c1 -> INNER JOIN t8 -> ON t8.c2 = t7.c1 -> WHERE t1.c1 IN (SELECT DISTINCT c3 -> FROM t9 -> WHERE t9.c1 = 13900180 -> AND t9.c3 = Ifnull(NULL, t9.c3)) -> AND t4.c1 IN (SELECT c3 -> FROM t10 -> WHERE c2 = 13900180); +----+--------------+-------------+------------+--------+-------------------+---------+---------+----------------------------------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+-------------+------------+--------+-------------------+---------+---------+----------------------------------+------+----------+----------------------------------------------------+ | 1 | SIMPLE | t9 | NULL | ref | PRIMARY,key1 | PRIMARY | 5 | const | 16 | 10.00 | Using where | | 1 | SIMPLE | | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | t1 | NULL | eq_ref | PRIMARY | PRIMARY | 2 | test.t9.c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t4 | NULL | eq_ref | PRIMARY,key1,key2 | PRIMARY | 5 | .c3 | 1 | 5.00 | Using where | | 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.t9.c3,test.t4.c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t3 | NULL | eq_ref | PRIMARY | PRIMARY | 6 | test.t9.c3,test.t4.c3,test.t4.c4 | 1 | 100.00 | Using index | | 1 | SIMPLE | t6 | NULL | eq_ref | PRIMARY | PRIMARY | 3 | test.t4.c5 | 1 | 100.00 | Using index | | 1 | SIMPLE | t5 | NULL | ref | PRIMARY | PRIMARY | 5 | .c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t7 | NULL | ref | PRIMARY,key1 | key1 | 6 | .c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t8 | NULL | ref | key1 | key1 | 6 | test.t7.c1 | 13 | 100.00 | NULL | | 3 | MATERIALIZED | t10 | NULL | ref | key1,key2 | key1 | 6 | const | 16 | 100.00 | NULL | +----+--------------+-------------+------------+--------+-------------------+---------+---------+----------------------------------+------+----------+----------------------------------------------------+ 11 rows in set, 1 warning (0.00 sec) # Execution results mysql> pager md5sum ; PAGER set to 'md5sum' mysql> SELECT * -> FROM t1 -> INNER JOIN t2 -> ON t2.c1 = t1.c1 -> INNER JOIN t3 -> ON t3.c1 = t2.c1 -> AND t3.c2 = t2.c2 -> INNER JOIN t4 -> -- force index (key2) -> ON t4.c2 = t3.c1 -> AND t4.c3 = t3.c2 -> AND t4.c4 = t3.c3 -> INNER JOIN t5 -> ON t5.c1 = t4.c1 -> INNER JOIN t6 -> ON t6.c1 = t4.c5 -> INNER JOIN t7 -> ON t7.c2 = t4.c1 -> INNER JOIN t8 -> ON t8.c2 = t7.c1 -> WHERE t1.c1 IN (SELECT DISTINCT c3 -> FROM t9 -> WHERE t9.c1 = 13900180 -> AND t9.c3 = Ifnull(NULL, t9.c3)) -> AND t4.c1 IN (SELECT c3 -> FROM t10 -> WHERE c2 = 13900180); c6176573c3fcbb08631ab7021429e6ae - 3488 rows in set (0.02 sec) # If another index is forced and t9 is not accessed in first place, the query returns the correct amount of rows: # Query SELECT * FROM t1 INNER JOIN t2 ON t2.c1 = t1.c1 INNER JOIN t3 ON t3.c1 = t2.c1 AND t3.c2 = t2.c2 INNER JOIN t4 force index (key2) ON t4.c2 = t3.c1 AND t4.c3 = t3.c2 AND t4.c4 = t3.c3 INNER JOIN t5 ON t5.c1 = t4.c1 INNER JOIN t6 ON t6.c1 = t4.c5 INNER JOIN t7 ON t7.c2 = t4.c1 INNER JOIN t8 ON t8.c2 = t7.c1 WHERE t1.c1 IN (SELECT DISTINCT c3 FROM t9 WHERE t9.c1 = 13900180 AND t9.c3 = Ifnull(NULL, t9.c3)) AND t4.c1 IN (SELECT c3 FROM t10 WHERE c2 = 13900180); # New execution plan mysql> explain SELECT * -> FROM t1 -> INNER JOIN t2 -> ON t2.c1 = t1.c1 -> INNER JOIN t3 -> ON t3.c1 = t2.c1 -> AND t3.c2 = t2.c2 -> INNER JOIN t4 -> force index (key2) -> ON t4.c2 = t3.c1 -> AND t4.c3 = t3.c2 -> AND t4.c4 = t3.c3 -> INNER JOIN t5 -> ON t5.c1 = t4.c1 -> INNER JOIN t6 -> ON t6.c1 = t4.c5 -> INNER JOIN t7 -> ON t7.c2 = t4.c1 -> INNER JOIN t8 -> ON t8.c2 = t7.c1 -> WHERE t1.c1 IN (SELECT DISTINCT c3 -> FROM t9 -> WHERE t9.c1 = 13900180 -> AND t9.c3 = Ifnull(NULL, t9.c3)) -> AND t4.c1 IN (SELECT c3 -> FROM t10 -> WHERE c2 = 13900180); +----+--------------+-------------+------------+--------+---------------+---------+---------+-----------------------------------------------------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+-------------+------------+--------+---------------+---------+---------+-----------------------------------------------------+------+----------+----------------------------------------------------+ | 1 | SIMPLE | | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using where | | 1 | SIMPLE | t1 | NULL | eq_ref | PRIMARY | PRIMARY | 2 | .c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | t5 | NULL | ref | PRIMARY | PRIMARY | 5 | .c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t3 | NULL | ref | PRIMARY | PRIMARY | 2 | .c3 | 68 | 100.00 | Using index | | 1 | SIMPLE | t4 | NULL | ref | key2 | key2 | 14 | .c3,test.t3.c2,test.t3.c3,.c3 | 1 | 100.00 | Using where | | 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | .c3,test.t3.c2 | 1 | 100.00 | Using index | | 1 | SIMPLE | t6 | NULL | eq_ref | PRIMARY | PRIMARY | 3 | test.t4.c5 | 1 | 100.00 | Using index | | 1 | SIMPLE | t7 | NULL | ref | PRIMARY,key1 | key1 | 6 | .c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t8 | NULL | ref | key1 | key1 | 6 | test.t7.c1 | 13 | 100.00 | NULL | | 2 | MATERIALIZED | t9 | NULL | ref | PRIMARY,key1 | PRIMARY | 5 | const | 16 | 10.00 | Using where | | 3 | MATERIALIZED | t10 | NULL | ref | key1,key2 | key1 | 6 | const | 16 | 100.00 | NULL | +----+--------------+-------------+------------+--------+---------------+---------+---------+-----------------------------------------------------+------+----------+----------------------------------------------------+ 12 rows in set, 1 warning (0.01 sec) # Correct resultset is returned mysql> pager md5sum ; PAGER set to 'md5sum' mysql> SELECT * -> FROM t1 -> INNER JOIN t2 -> ON t2.c1 = t1.c1 -> INNER JOIN t3 -> ON t3.c1 = t2.c1 -> AND t3.c2 = t2.c2 -> INNER JOIN t4 -> force index (key2) -> ON t4.c2 = t3.c1 -> AND t4.c3 = t3.c2 -> AND t4.c4 = t3.c3 -> INNER JOIN t5 -> ON t5.c1 = t4.c1 -> INNER JOIN t6 -> ON t6.c1 = t4.c5 -> INNER JOIN t7 -> ON t7.c2 = t4.c1 -> INNER JOIN t8 -> ON t8.c2 = t7.c1 -> WHERE t1.c1 IN (SELECT DISTINCT c3 -> FROM t9 -> WHERE t9.c1 = 13900180 -> AND t9.c3 = Ifnull(NULL, t9.c3)) -> AND t4.c1 IN (SELECT c3 -> FROM t10 -> WHERE c2 = 13900180); 92ac8ecc163c231f0864f1f574dd8e8e - 218 rows in set (0.03 sec) # MySQL Community 5.7.23 is showing same issue mysql [localhost] {msandbox} (test) > status ; -------------- /opt/mysql/5.7.23/bin/mysql Ver 14.14 Distrib 5.7.23, for linux-glibc2.12 (x86_64) using EditLine wrapper Connection id: 6 Current database: test Current user: msandbox@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.23 MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql_sandbox4013.sock Uptime: 22 hours 20 min 2 sec Threads: 2 Questions: 459 Slow queries: 0 Opens: 202 Flush tables: 1 Open tables: 170 Queries per second avg: 0.005 mysql [localhost] {msandbox} (test) > explain -> SELECT * -> FROM t1 -> INNER JOIN t2 -> ON t2.c1 = t1.c1 -> INNER JOIN t3 -> ON t3.c1 = t2.c1 -> AND t3.c2 = t2.c2 -> INNER JOIN t4 -> -- force index (key2) -> ON t4.c2 = t3.c1 -> AND t4.c3 = t3.c2 -> AND t4.c4 = t3.c3 -> INNER JOIN t5 -> ON t5.c1 = t4.c1 -> INNER JOIN t6 -> ON t6.c1 = t4.c5 -> INNER JOIN t7 -> ON t7.c2 = t4.c1 -> INNER JOIN t8 -> ON t8.c2 = t7.c1 -> WHERE t1.c1 IN (SELECT DISTINCT c3 -> FROM t9 -> WHERE t9.c1 = 13900180 -> AND t9.c3 = Ifnull(NULL, t9.c3)) -> AND t4.c1 IN (SELECT c3 -> FROM t10 -> WHERE c2 = 13900180); +----+--------------+-------------+------------+--------+-------------------+---------+---------+----------------------------------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+-------------+------------+--------+-------------------+---------+---------+----------------------------------+------+----------+----------------------------------------------------+ | 1 | SIMPLE | t9 | NULL | ref | PRIMARY,key1 | PRIMARY | 5 | const | 16 | 10.00 | Using where | | 1 | SIMPLE | | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | t1 | NULL | eq_ref | PRIMARY | PRIMARY | 2 | test.t9.c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t4 | NULL | eq_ref | PRIMARY,key1,key2 | PRIMARY | 5 | .c3 | 1 | 5.00 | Using where | | 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.t9.c3,test.t4.c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t3 | NULL | eq_ref | PRIMARY | PRIMARY | 6 | test.t9.c3,test.t4.c3,test.t4.c4 | 1 | 100.00 | Using index | | 1 | SIMPLE | t6 | NULL | eq_ref | PRIMARY | PRIMARY | 3 | test.t4.c5 | 1 | 100.00 | Using index | | 1 | SIMPLE | t5 | NULL | ref | PRIMARY | PRIMARY | 5 | .c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t7 | NULL | ref | PRIMARY,key1 | key1 | 6 | .c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t8 | NULL | ref | key1 | key1 | 6 | test.t7.c1 | 13 | 100.00 | NULL | | 3 | MATERIALIZED | t10 | NULL | ref | key1,key2 | key1 | 6 | const | 16 | 100.00 | NULL | +----+--------------+-------------+------------+--------+-------------------+---------+---------+----------------------------------+------+----------+----------------------------------------------------+ 11 rows in set, 1 warning (0.00 sec) mysql [localhost] {msandbox} (test) > pager md5sum ; PAGER set to 'md5sum' mysql [localhost] {msandbox} (test) > SELECT * -> FROM t1 -> INNER JOIN t2 -> ON t2.c1 = t1.c1 -> INNER JOIN t3 -> ON t3.c1 = t2.c1 -> AND t3.c2 = t2.c2 -> INNER JOIN t4 -> -- force index (key2) -> ON t4.c2 = t3.c1 -> AND t4.c3 = t3.c2 -> AND t4.c4 = t3.c3 -> INNER JOIN t5 -> ON t5.c1 = t4.c1 -> INNER JOIN t6 -> ON t6.c1 = t4.c5 -> INNER JOIN t7 -> ON t7.c2 = t4.c1 -> INNER JOIN t8 -> ON t8.c2 = t7.c1 -> WHERE t1.c1 IN (SELECT DISTINCT c3 -> FROM t9 -> WHERE t9.c1 = 13900180 -> AND t9.c3 = Ifnull(NULL, t9.c3)) -> AND t4.c1 IN (SELECT c3 -> FROM t10 -> WHERE c2 = 13900180); c6176573c3fcbb08631ab7021429e6ae - 3488 rows in set (0.03 sec) # Using another execution plan I can obtain correct resultset mysql [localhost] {msandbox} (test) > explain -> SELECT * -> FROM t1 -> INNER JOIN t2 -> ON t2.c1 = t1.c1 -> INNER JOIN t3 -> ON t3.c1 = t2.c1 -> AND t3.c2 = t2.c2 -> INNER JOIN t4 -> force index (key2) -> ON t4.c2 = t3.c1 -> AND t4.c3 = t3.c2 -> AND t4.c4 = t3.c3 -> INNER JOIN t5 -> ON t5.c1 = t4.c1 -> INNER JOIN t6 -> ON t6.c1 = t4.c5 -> INNER JOIN t7 -> ON t7.c2 = t4.c1 -> INNER JOIN t8 -> ON t8.c2 = t7.c1 -> WHERE t1.c1 IN (SELECT DISTINCT c3 -> FROM t9 -> WHERE t9.c1 = 13900180 -> AND t9.c3 = Ifnull(NULL, t9.c3)) -> AND t4.c1 IN (SELECT c3 -> FROM t10 -> WHERE c2 = 13900180); +----+--------------+-------------+------------+--------+---------------+---------+---------+-----------------------------------------------------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+-------------+------------+--------+---------------+---------+---------+-----------------------------------------------------+------+----------+----------------------------------------------------+ | 1 | SIMPLE | | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using where | | 1 | SIMPLE | t1 | NULL | eq_ref | PRIMARY | PRIMARY | 2 | .c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | t5 | NULL | ref | PRIMARY | PRIMARY | 5 | .c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t2 | NULL | ref | PRIMARY | PRIMARY | 2 | .c3 | 7 | 100.00 | Using index | | 1 | SIMPLE | t3 | NULL | ref | PRIMARY | PRIMARY | 4 | .c3,test.t2.c2 | 8 | 100.00 | Using index | | 1 | SIMPLE | t4 | NULL | ref | key2 | key2 | 14 | .c3,test.t2.c2,test.t3.c3,.c3 | 1 | 100.00 | Using where | | 1 | SIMPLE | t6 | NULL | eq_ref | PRIMARY | PRIMARY | 3 | test.t4.c5 | 1 | 100.00 | Using index | | 1 | SIMPLE | t7 | NULL | ref | PRIMARY,key1 | key1 | 6 | .c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t8 | NULL | ref | key1 | key1 | 6 | test.t7.c1 | 13 | 100.00 | NULL | | 2 | MATERIALIZED | t9 | NULL | ref | PRIMARY,key1 | PRIMARY | 5 | const | 16 | 10.00 | Using where | | 3 | MATERIALIZED | t10 | NULL | ref | key1,key2 | key1 | 6 | const | 16 | 100.00 | NULL | +----+--------------+-------------+------------+--------+---------------+---------+---------+-----------------------------------------------------+------+----------+----------------------------------------------------+ 12 rows in set, 1 warning (0.01 sec) mysql [localhost] {msandbox} (test) > pager md5sum ; PAGER set to 'md5sum' mysql [localhost] {msandbox} (test) > SELECT * -> FROM t1 -> INNER JOIN t2 -> ON t2.c1 = t1.c1 -> INNER JOIN t3 -> ON t3.c1 = t2.c1 -> AND t3.c2 = t2.c2 -> INNER JOIN t4 -> force index (key2) -> ON t4.c2 = t3.c1 -> AND t4.c3 = t3.c2 -> AND t4.c4 = t3.c3 -> INNER JOIN t5 -> ON t5.c1 = t4.c1 -> INNER JOIN t6 -> ON t6.c1 = t4.c5 -> INNER JOIN t7 -> ON t7.c2 = t4.c1 -> INNER JOIN t8 -> ON t8.c2 = t7.c1 -> WHERE t1.c1 IN (SELECT DISTINCT c3 -> FROM t9 -> WHERE t9.c1 = 13900180 -> AND t9.c3 = Ifnull(NULL, t9.c3)) -> AND t4.c1 IN (SELECT c3 -> FROM t10 -> WHERE c2 = 13900180); 92ac8ecc163c231f0864f1f574dd8e8e - 218 rows in set (0.02 sec)