Mysql – Why turning off condition_fanout_filter speed up query

configurationMySQLperformance-tuningquery-performance

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.

Best Answer

you have one of the cases where the optimizer searching for a good solution costs much more time than the query it self, but that can change with increasing numbers.

You should read about what this optimization switch does.

if you need more speed you can try if one of https://dev.mysql.com/doc/refman/8.0/en/switchable-optimizations.html work better. additinal to different indexes

Related Question