|
Found this in MDEV-9420: A query uses index_merge access even if using single index would read just a few rows.
Copying the latest two commentes from MDEV-9420:
Trying with the original dataset, 10.1+MDEV-9457 patch:
|
MariaDB [j10]> explain select * from point_activite
|
WHERE `id_activity_list` = 1479 AND `id_contact_Script` = 2075347 AND `id_contact_campaign_pass` = 1920183
|
ORDER BY `IDPoint_Activite`\G
|
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: point_activite
|
type: index_merge
|
possible_keys: id_contact_Script,id_activity_list,id_contact_campaign_pass
|
key: id_contact_campaign_pass,id_contact_Script
|
key_len: 9,9
|
ref: NULL
|
rows: 1
|
Extra: Using intersect(id_contact_campaign_pass,id_contact_Script); Using where; Using filesort
|
Without ORDER BY, the plan is now the same:
MariaDB [j10]> explain select * from point_activite
|
WHERE `id_activity_list` = 1479 AND `id_contact_Script` = 2075347 AND `id_contact_campaign_pass` = 1920183 \G
|
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: point_activite
|
type: index_merge
|
possible_keys: id_contact_Script,id_activity_list,id_contact_campaign_pass
|
key: id_contact_campaign_pass,id_contact_Script
|
key_len: 9,9
|
ref: NULL
|
rows: 1
|
Extra: Using intersect(id_contact_campaign_pass,id_contact_Script); Using where
|
(10.0 has the same plan).
Let's look at the merged conditions:
MariaDB [j10]> explain select * from point_activite WHERE `id_activity_list` = 1479 ;
|
+------+-------------+----------------+------+------------------+------------------+---------+-------+------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+----------------+------+------------------+------------------+---------+-------+------+-------+
|
| 1 | SIMPLE | point_activite | ref | id_activity_list | id_activity_list | 9 | const | 290 | |
|
+------+-------------+----------------+------+------------------+------------------+---------+-------+------+-------+
|
|
MariaDB [j10]> explain select * from point_activite WHERE `id_contact_Script` = 2075347 ;
|
+------+-------------+----------------+------+-------------------+-------------------+---------+-------+------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+----------------+------+-------------------+-------------------+---------+-------+------+-------+
|
| 1 | SIMPLE | point_activite | ref | id_contact_Script | id_contact_Script | 9 | const | 16 | |
|
+------+-------------+----------------+------+-------------------+-------------------+---------+-------+------+-------+
|
|
MariaDB [j10]> explain select * from point_activite WHERE `id_contact_campaign_pass` = 1920183 ;
|
+------+-------------+----------------+------+--------------------------+--------------------------+---------+-------+------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+----------------+------+--------------------------+--------------------------+---------+-------+------+-------+
|
| 1 | SIMPLE | point_activite | ref | id_contact_campaign_pass | id_contact_campaign_pass | 9 | const | 3 | |
|
+------+-------------+----------------+------+--------------------------+--------------------------+---------+-------+------+-------+
|
So, index_merge computes intersection of a set of 3 rows and a set of 16 rows.
This looks a bit weird - isn't it easier to read 3 rows from the table?
OTOH, reading a few rows from the index = one disk read, reading each table record is also one disk read. Then, doing index_merge is doing two index reads plus reading one row, and it is possible that that's cheaper than doing an index read plus reading 3 rows from disk.
The above reasoning ignores the costs of setting up index_merge, but so do our cost formulas. We could add a small penalty for initializing extra handler objects for index_merge.
|