Details
-
Type:
Bug
-
Status: Open
-
Priority:
Trivial
-
Resolution: Unresolved
-
Affects Version/s: 5.3.12, 5.5.36, 10.0.10
-
Fix Version/s: None
-
Component/s: None
-
Labels:
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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 | +------+-------------+-------+-------------+------------------+------------------+---------+------+------+----------+-------------------------------------------------------------+
Launchpad bug id: 1007806