LP:1005898 - index_merge/intersection is used when ref(const) is faster

SELECT count(*) AS amount FROM lots WHERE contractNumber='1478876' AND lots.tsClosed IS NULL;

+--------+

| amount |

+--------+

|   9552 |

+--------+

1 row in set (1.03 sec)

mysql> set optimizer_switch="index_merge=off";Query OK, 0 rows affected (0.00 sec)

mysql> SELECT count(*) AS amount FROM lots WHERE contractNumber='1478876' AND lots.tsClosed IS NULL;

+--------+

| amount |

+--------+

|   9552 |

+--------+

1 row in set (0.03 sec)

mysql> SELECT SQL_NO_CACHE count(*) AS amount FROM lots WHERE contractNumber='1478876' AND lots.tsClosed IS NULL;

+--------+

| amount |

+--------+

|   9552 |

+--------+

MariaDB [lots]> explain SELECT count(*) AS amount FROM lots WHERE contractNumber='1478876' AND lots.tsClosed IS NULL;

+----+-------------+-------+-------------+-------------------------+-------------------------+---------+------+------+--------------------------------------------------------------------+

| id | select_type | table | type        | possible_keys           | key                     | key_len | ref  | rows | Extra                                                              |

+----+-------------+-------+-------------+-------------------------+-------------------------+---------+------+------+--------------------------------------------------------------------+

|  1 | SIMPLE      | lots  | index_merge | tsClosed,contractNumber | contractNumber,tsClosed | 5,5     | NULL | 3257 | Using intersect(contractNumber,tsClosed); Using where; Using index |

+----+-------------+-------+-------------+-------------------------+-------------------------+---------+------+------+--------------------------------------------------------------------+

1.85 sec.

MariaDB [lots]> explain SELECT count(*) AS amount FROM lots ignore index(tsClosed) WHERE contractNumber='1478876' AND lots.tsClosed IS NULL;

+----+-------------+-------+------+----------------+----------------+---------+-------+-------+-------------+

| id | select_type | table | type | possible_keys  | key            | key_len | ref   | rows  | Extra       |

+----+-------------+-------+------+----------------+----------------+---------+-------+-------+-------------+

|  1 | SIMPLE      | lots  | ref  | contractNumber | contractNumber | 5       | const | 28600 | Using where |

+----+-------------+-------+------+----------------+----------------+---------+-------+-------+-------------+

0.30 sec

MariaDB [lots]> explain SELECT count(*) AS amount FROM lots ignore index(contractNumber) WHERE contractNumber='1478876' AND lots.tsClosed IS NULL;

+----+-------------+-------+------+---------------+----------+---------+-------+--------+------------------------------------+

| id | select_type | table | type | possible_keys | key      | key_len | ref   | rows   | Extra                              |

+----+-------------+-------+------+---------------+----------+---------+-------+--------+------------------------------------+

|  1 | SIMPLE      | lots  | ref  | tsClosed      | tsClosed | 5       | const | 243422 | Using index condition; Using where |

+----+-------------+-------+------+---------------+----------+---------+-------+--------+------------------------------------+

4.50 sec