Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Not a Bug
-
5.2.14, 5.3.12
Description
The queries were provided by Stephane Varoqui:
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 |
|
+--------+ |
The dataset is lots.tgz, uploaded to ftp.askmonty.org/private/
EXPLAIN outputs and query time:
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
|
As one can see, index_merge/intersect is about 1.85/0.30=6 times slower than ref access on contractNumber.