mysql – Why turning off condition_fanout_filter speed up query

bin/mysqld_safe --user=mysql --optimizer-switch=condition_fanout_filter=off --innodb-flush-method=O_DIRECT &

Query (from TPC-H query-19):

SELECT Sum(l_extendedprice * ( 1 - l_discount )) AS revenue
FROM   lineitem,
       part
WHERE  ( p_partkey = l_partkey
         AND p_brand = 'Brand#52'
         AND p_container IN ( 'SM CASE', 'SM BOX', 'SM PACK', 'SM PKG' )
         AND l_quantity >= 4
         AND l_quantity <= 4 + 10
         AND p_size BETWEEN 1 AND 5
         AND l_shipmode IN ( 'AIR', 'AIR REG' )
         AND l_shipinstruct = 'DELIVER IN PERSON' )
        OR ( p_partkey = l_partkey
             AND p_brand = 'Brand#11'
             AND p_container IN ( 'MED BAG', 'MED BOX', 'MED PKG', 'MED PACK' )
             AND l_quantity >= 18
             AND l_quantity <= 18 + 10
             AND p_size BETWEEN 1 AND 10
             AND l_shipmode IN ( 'AIR', 'AIR REG' )
             AND l_shipinstruct = 'DELIVER IN PERSON' )
        OR ( p_partkey = l_partkey
             AND p_brand = 'Brand#51'
             AND p_container IN ( 'LG CASE', 'LG BOX', 'LG PACK', 'LG PKG' )
             AND l_quantity >= 29
             AND l_quantity <= 29 + 10
             AND p_size BETWEEN 1 AND 15
             AND l_shipmode IN ( 'AIR', 'AIR REG' )
             AND l_shipinstruct = 'DELIVER IN PERSON' ); 

condition_fanout_filter=off

RESULT:  1 row in set (13.73 sec)

explain:

+----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key          | key_len | ref                 | rows   | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+
|  1 | SIMPLE      | PART     | NULL       | ALL  | PRIMARY       | NULL         | NULL    | NULL                | 198000 |   100.00 | Using where |
|  1 | SIMPLE      | LINEITEM | NULL       | ref  | LINEITEM_FK2  | LINEITEM_FK2 | 4       | tpch.PART.P_PARTKEY |     30 |   100.00 | Using where |
+----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+

condition_fanout_filter=on

RESULT:  1 row in set (30.57 sec)

explain:

+----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key          | key_len | ref                 | rows   | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+
|  1 | SIMPLE      | PART     | NULL       | ALL  | PRIMARY       | NULL         | NULL    | NULL                | 198000 |     1.33 | Using where |
|  1 | SIMPLE      | LINEITEM | NULL       | ref  | LINEITEM_FK2  | LINEITEM_FK2 | 4       | tpch.PART.P_PARTKEY |     30 |     0.67 | Using where |
+----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+

From column ‘filtered’, “condition_fanout_filter=on” should be faster.

Other important things

this only happens in the 15k RPM SAS 600GB HDD and with --innodb-flush-method=O_DIRECT.
Either using an SSD or not set O_DIRECT, this problem would disappear.