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:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.50, 5.1.22, 5.0.66a OS:Any
Assigned to: Sergey Glukhov
Tags: bfsm_2007_11_15, bfsm_2007_12_06, index merge, regression
Triage: Triaged: D3 (Medium) / R2 (Low) / E2 (Low)

[10 Nov 2007 2:06] Kolbe Kegel
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.
[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] Valerii 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.