[MDEV-699] LP:1007806 - index_merge/interesection is picked when it seems to be the best plan Created: 2012-06-02  Updated: 2017-11-05

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.3.12, 5.5.36, 10.0.10
Fix Version/s: 10.2

Type: Bug Priority: Trivial
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug1007806.xml    

 Description   

Look at the testcase for https://bugs.launchpad.net/maria/+bug/1006164. The DELETE statement (but not its SELECT analog for some reason) uses index_merge/intersect strategy. This choice looks a bit odd, because each of the merged scans selects only a few records.

I'm not stating the choice is wrong, but this example is something we could look at when/if we're going to tune index_merge cost model.

 Comments   
Comment by Rasmus Johansson (Inactive) [ 2012-06-02 ]

Launchpad bug id: 1007806

Comment by Elena Stepanova [ 2014-04-12 ]

From what I see, SELECT from the testcase for bug#1006164 also uses index_merge/intersect (even on a version before the fix, 5.3.7).

Also, the summary of the bug report reads strange, apparently there is a typo in there somewhere ("index_merge/interesection is picked when it seems to be the best plan").

I will assume that the major complaint/doubt is that DELETE uses index_merge/intersect when it (maybe) shouldn't. It seems to be the case still, so I'm setting affected versions to the current ones.

5.3.7 (before the fix):

MariaDB [test]> explain extended select * from t1 where t1.zone_id=830 AND modified=9;
 
+----+-------------+-------+-------------+------------------+------------------+---------+------+------+----------+-------------------------------------------------------------+
 
| id | select_type | table | type        | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                                                       |
 
+----+-------------+-------+-------------+------------------+------------------+---------+------+------+----------+-------------------------------------------------------------+
 
|  1 | SIMPLE      | t1    | index_merge | zone_id,modified | zone_id,modified | 5,2     | NULL |    1 |   100.00 | Using intersect(zone_id,modified); Using where; Using index |
 
+----+-------------+-------+-------------+------------------+------------------+---------+------+------+----------+-------------------------------------------------------------+

10.0.10+ (current latest):

MariaDB [test]> explain extended select * from t1 where t1.zone_id=830 AND modified=9;
 
+------+-------------+-------+-------------+------------------+------------------+---------+------+------+----------+-------------------------------------------------------------+
 
| id   | select_type | table | type        | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                                                       |
 
+------+-------------+-------+-------------+------------------+------------------+---------+------+------+----------+-------------------------------------------------------------+
 
|    1 | SIMPLE      | t1    | index_merge | zone_id,modified | zone_id,modified | 5,2     | NULL |    1 |   100.00 | Using intersect(zone_id,modified); Using where; Using index |
 
+------+-------------+-------+-------------+------------------+------------------+---------+------+------+----------+-------------------------------------------------------------+
 
 
 
MariaDB [test]> explain extended DELETE t1 FROM t1 WHERE t1.zone_id=830 AND modified=9;
 
+------+-------------+-------+-------------+------------------+------------------+---------+------+------+----------+-------------------------------------------------------------+
 
| id   | select_type | table | type        | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                                                       |
 
+------+-------------+-------+-------------+------------------+------------------+---------+------+------+----------+-------------------------------------------------------------+
 
|    1 | SIMPLE      | t1    | index_merge | zone_id,modified | zone_id,modified | 5,2     | NULL |    1 |   100.00 | Using intersect(zone_id,modified); Using where; Using index |
 
+------+-------------+-------+-------------+------------------+------------------+---------+------+------+----------+-------------------------------------------------------------+

Generated at Thu Feb 08 06:30:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.