Bug #16166 cannot force index merge
Submitted: 3 Jan 2006 22:12 Modified: 13 Jan 2006 0:17
Reporter: Kolbe Kegel Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.0.17 OS:Linux (Linux)
Assigned to: Sergey Petrunya

[3 Jan 2006 22:12] Kolbe Kegel
Description:
It is sometimes not possible to force index merge.

This limitation can make it impossible to use index merge in queries with a range scan (per documentat, FORCE INDEX is required in some situations where a range scan occurs).

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (cola char(3) not null, colb char(3) not null, key (cola), key (colb));

INSERT INTO t1 VALUES ('foo','bar'),('fuz','baz');

explain select * from t1 WHERE cola = 'foo' AND colb = 'bar';
explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar';

-- we need to have to have a few thousand more rows for index merge to work...
INSERT INTO t1 SELECT * from t1 WHERE cola = 'foo';
INSERT INTO t1 SELECT * from t1 WHERE cola = 'foo';
INSERT INTO t1 SELECT * from t1 WHERE cola = 'foo';
INSERT INTO t1 SELECT * from t1 WHERE cola = 'foo';
INSERT INTO t1 SELECT * from t1 WHERE cola = 'foo';
INSERT INTO t1 SELECT * from t1 WHERE cola = 'foo';
INSERT INTO t1 SELECT * from t1 WHERE cola = 'foo';
INSERT INTO t1 SELECT * from t1 WHERE cola = 'foo';
INSERT INTO t1 SELECT * from t1 WHERE cola = 'foo';

INSERT INTO t1 SELECT * from t1 WHERE cola <> 'foo';
INSERT INTO t1 SELECT * from t1 WHERE cola <> 'foo';
INSERT INTO t1 SELECT * from t1 WHERE cola <> 'foo';
INSERT INTO t1 SELECT * from t1 WHERE cola <> 'foo';
INSERT INTO t1 SELECT * from t1 WHERE cola <> 'foo';
INSERT INTO t1 SELECT * from t1 WHERE cola <> 'foo';
INSERT INTO t1 SELECT * from t1 WHERE cola <> 'foo';
INSERT INTO t1 SELECT * from t1 WHERE cola <> 'foo';
INSERT INTO t1 SELECT * from t1 WHERE cola <> 'foo';
INSERT INTO t1 SELECT * from t1 WHERE cola <> 'foo';
INSERT INTO t1 SELECT * from t1 WHERE cola <> 'foo';
INSERT INTO t1 SELECT * from t1 WHERE cola <> 'foo';
INSERT INTO t1 SELECT * from t1 WHERE cola <> 'foo';

OPTIMIZE TABLE t1;
explain select * from t1 WHERE cola = 'foo' AND colb = 'bar';
explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar';

-- Apparently there is a certain threshhold before index merge is used.... this should be documented somewhere if this does not already exist
INSERT INTO t1 SELECT * from t1 WHERE cola <> 'foo';

OPTIMIZE TABLE t1;

explain select * from t1 WHERE cola = 'foo' AND colb = 'bar';
explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar';

Suggested fix:
Using FORCE INDEX should always cause the optimizer to do an index merge if the index merge is logically possible.
[4 Jan 2006 6:57] Igor Babaev
FORCE INDEX(idx1, idx2) tells the server that using table scan is much more expensive than using index idx1 OR idx2 OR both of them.
[4 Jan 2006 7:19] Kolbe Kegel
The problem is that FORCE INDEX *prevents* index merge from happening in a situation where index merge is normally selected by the optimizer.

The first two explains of the original example give the following output:

mysql 5.0.17-max (root) "test"> explain select * from t1 WHERE cola = 'foo' AND colb = 'bar' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ref
possible_keys: cola,colb
          key: cola
      key_len: 3
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

mysql 5.0.17-max (root) "test"> explain select * from t1 force index (cola, colb) WHERE cola = 'foo' AND colb = 'bar' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ref
possible_keys: cola,colb
          key: cola
      key_len: 3
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

The last two explains of the original example give the following output:

mysql 5.0.17-max (root) "test"> explain select * from t1 WHERE cola = 'foo' AND colb = 'bar' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index_merge
possible_keys: cola,colb
          key: cola,colb
      key_len: 3,3
          ref: NULL
         rows: 14
        Extra: Using intersect(cola,colb); Using where; Using index
1 row in set (0.00 sec)

mysql 5.0.17-max (root) "test"> explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ref
possible_keys: cola,colb
          key: cola
      key_len: 3
          ref: const
         rows: 555
        Extra: Using where
1 row in set (0.00 sec)
[11 Jan 2006 12:58] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/910
[12 Jan 2006 7:46] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/946
[12 Jan 2006 10:12] Sergey Petrunya
The fix has been pushed into 5.0.19 tree.

Note for the changelog:
For query that used Index Merge intersection algorithm for indexes idx1... idxN , adding "FORCE INDEX (idx1,...,idxN) caused the Index Merge intersection not to be used anymore ("ref" on one of idx{i} was used instead).
[13 Jan 2006 0:17] Mike Hillyer
Added to 5.0.19 changelog:

<listitem>
        <para>
          The <literal>FORCE INDEX</literal> keyword in a query would
          prevent an index merge from being used where an index merge
          would normally be chosen by the optimizer. (Bug #16166)
        </para>
      </listitem>