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 | PRIMARY | 8 | test.t1.f1 | 1 | Using where | | 1 | SIMPLE | t3 | ref | f1 | f1 | 628 | test.t1.id,const | 11 | Using where | | 1 | SIMPLE | thumbnails_t3 | ref | f3 | f3 | 9 | test.t3.id | 1 | Using index | +----+-------------+---------------+--------+---------------+---------+---------+------------------+------+-------------+ 4 rows in set (0.00 sec) mysql> flush status; 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.00 sec) mysql> show status like 'Handler_read%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 0 | | Handler_read_key | 514 | | Handler_read_last | 0 | | Handler_read_next | 512 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +-----------------------+-------+ 7 rows in set (0.00 sec) mysql> flush status; 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 (1.04 sec) mysql> show status like 'Handler_read%'; +-----------------------+--------+ | Variable_name | Value | +-----------------------+--------+ | Handler_read_first | 0 | | Handler_read_key | 514 | | Handler_read_last | 0 | | Handler_read_next | 512512 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +-----------------------+--------+ 7 rows in set (0.00 sec)