| Bug #32254 | Index merge used unnecessarily | ||
|---|---|---|---|
| Submitted: | 10 Nov 2007 2:06 | Modified: | 27 Oct 2011 18:57 |
| Reporter: | Kolbe Kegel | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.0.50, 5.1.22, 5.0.66a | OS: | Any |
| Assigned to: | Sergei Glukhov | CPU Architecture: | Any |
| Tags: | bfsm_2007_11_15, bfsm_2007_12_06, index merge, regression | ||
[10 Nov 2007 2:08]
Kolbe Kegel
Sorry, one of the SQL statements in "How to repeat" referred to the wrong table. Here are the correct statements to run after loading the data from the file: explain select count(*) from answers WHERE answers.status_id IN (4) AND answers.lang_id = 16 AND answers.status_type = 4\G create index answers$lang_id on answers (lang_id); explain select count(*) from answers WHERE answers.status_id IN (4) AND answers.lang_id = 16 AND answers.status_type = 4\G
[10 Nov 2007 2:08]
Kolbe Kegel
SQL dump to reproduce bug #32254
Attachment: bug32254.sql.gz (application/x-gzip, text), 113.67 KiB.
[12 Nov 2007 6:54]
Trent Lloyd
Verified with another case
[19 Jun 2008 15:59]
martin sarsale
Do you have news about this report? we're experiencing the same problem in Server version: 5.1.22-rc-log FreeBSD port: mysql-server-5.1.22 thanks in advance
[20 Jun 2008 3:25]
Valeriy Kravchuk
Bug #34869 was marked as a duplicate of this one.
[4 Nov 2008 11:19]
Andrii Nikitin
IMHO it is very easy to fix this bug (at least most obvious part of it), just apply two simple rules: 1. do not use INDEX_MERGE for two indexes if compound index present for those columns: create table t1( c1 int, c2 int, KEY i1(c1), KEY i2(c2), KEY icompound(c1,c2) ); intersect(i1,i2) will be never faster than using icompound. Using it is obvious bug. (I agree that keeping all three indexes is redundant, but perhaps we need i1 and i2 for FK constraints and icompound for performance optimization since it is much faster). 2. do not use INDEX_MERGE for two indexes if first index is compound and columns from second index is sequential part of first index: create table t1( c1 int, c2 int, KEY i1(c1), KEY icompound(c2,c1) ); intersect(i1,icompound(4)) will be never faster than using just icompound. Using it is obvious bug. Maybe it will not solve all possible issues related to this bug, but will solve most obvious ones. Also I am sure that some users would love to live with Bug #28189 (which fix seems did lead to this problem), so maybe if we reject implementing suggested rules, maybe we will implement feature request Bug #40051.
[10 Jun 2009 5:48]
Andrii Nikitin
workaround for 5.1.34+ : use optimizer_switch=index_merge_intersection=off
[27 Oct 2011 18:57]
Paul DuBois
Noted in 5.6.4 changelog. For some queries, the index_merge access method was used even when more expensive then ref access.
Description: The optimizer will choose to use index merge in some situations where using a composite key would be more straightforward and more efficient. How to repeat: Load data from attached file. Try these queries: explain select count(*) from t1 WHERE t1.status_id IN (4) AND t1.lang_id = 16 AND t1.status_type = 4\G create index answers$lang_id on answers (lang_id); explain select count(*) from answers WHERE answers.status_id IN (4) AND answers.lang_id = 16 AND answers.status_type = 4\G mysql 5.0.50-enterprise-gpl (root) [test]> explain select count(*) from answers WHERE answers.status_id IN (4) AND answers.lang_id = 16 AND answers.status_type = 4\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: answers type: index_merge possible_keys: answers$status_id,answers$status_type,answers$status_type$lang_id$status_id key: answers$status_id,answers$status_type,answers$status_type$lang_id$status_id key_len: 2,2,6 ref: NULL rows: 1066 Extra: Using intersect(answers$status_id,answers$status_type,answers$status_type$lang_id$status_id); Using where; Using index 1 row in set (0.00 sec) mysql 5.0.50-enterprise-gpl (root) [test]> create index answers$lang_id on answers (lang_id); Query OK, 46226 rows affected (1.77 sec) Records: 46226 Duplicates: 0 Warnings: 0 mysql 5.0.50-enterprise-gpl (root) [test]> explain select count(*) from answers WHERE answers.status_id IN (4) AND answers.lang_id = 16 AND answers.status_type = 4\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: answers type: index_merge possible_keys: answers$status_id,answers$status_type,answers$status_type$lang_id$status_id,answers$lang_id key: answers$lang_id,answers$status_id,answers$status_type key_len: 2,2,2 ref: NULL rows: 4113 Extra: Using intersect(answers$lang_id,answers$status_id,answers$status_type); Using where; Using index 1 row in set (0.00 sec) Suggested fix: Index merge should not be used if a single existing index would already fully cover the WHERE criteria.