Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.10
Description
Discovered as part of MDEV-9420. See these comments:
Copying here:
Checking problem #1.
MariaDB 10.0:
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
|
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
|
So, in 10.0, the plan is always to use index_merge to produce one row. Actually, two rows are produced and the query finishes in 0.05 sec or less.
In 10.1.10:
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
|
The same so far. Adding ORDER BY:
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
|
possible_keys: id_contact_Script,id_activity_list,id_contact_campaign_pass
|
key: PRIMARY
|
key_len: 8
|
ref: NULL
|
rows: 1997722
|
Extra: Using where
|
And the plan becomes much worse. It wants to read nearly 2M rows. Execution takes 1 min 39 sec.