Bug #21486 No index merge for ENUM columns
Submitted: 7 Aug 2006 16:57 Modified: 1 Nov 2006 10:12
Reporter: Thomas Auge Email Updates:
Status: No Feedback Impact on me:
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.0.22 OS:Linux (Linux)
Assigned to: CPU Architecture:Any
Tags: enum, INDEX, merge, Optimizer

[7 Aug 2006 16:57] Thomas Auge
Queries that would normally be index merges become table scans when an ENUM column is involved.

Discovered, tested, and confirmed on a large innoDB table.

How to repeat:
CREATE TABLE `etest` (
`enum1` ENUM( 'hello', 'you' ) NOT NULL ,
`enum2` ENUM( 'look!', 'an', 'enum' ) NOT NULL

ALTER TABLE `etest` ADD INDEX ( `enum1` ) 
ALTER TABLE `etest` ADD INDEX ( `enum2` ) 

INSERT INTO `etest` ( `enum1` , `enum2` ) VALUES 
('hello', 'look!'), ('you', 'an');

EXPLAIN SELECT * FROM etest WHERE enum1 = 'hello' OR enum2 = 'look!'
... SIMPLE  	etest  	ALL  	enum1,enum2  	NULL  	NULL  	NULL ...

Suggested fix:
[26 Aug 2006 12:25] Valeriy Kravchuk
Thank you for a problem report. Sorry, but I was not able to repeat the behaviour described with larger number of rows in the table (with 5.0.25-BK on Linux):

mysql> insert into etest select * from etest where enum1 <> 'hello';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> insert into etest select * from etest where enum1 <> 'hello';
Query OK, 512 rows affected (0.01 sec)
Records: 512  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM etest WHERE enum1 = 'hello' OR enum2 = 'look!'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: etest
         type: index_merge
possible_keys: enum1,enum2
          key: enum1,enum2
      key_len: 1,1
          ref: NULL
         rows: 2
        Extra: Using union(enum1,enum2); Using where
1 row in set (0.00 sec)

So, merge optimisation is used. It is not used with table containing 2 rows even for INT columns.
[26 Aug 2006 14:55] Thomas Auge
The test case was derrived from a much more complex query. Obviously the problem isn't as simple as the test case I created. Give me a day or two to further recreat it on an artificial table.
[31 Aug 2006 22:02] Thomas Auge
It seems the problem is not the enum column, but a join. An outer join with no where condition on the joined table should not invalid a key choice that works without that join (right?).

Index on c1

SELECT * FROM t1 WHERE c1 = const OR c1 = const

uses index.

SELECT * FROM t1 LEFT JOIN t2 ON t2.ID = t1.whatever WHERE c1 = const OR c1 = const

no index use.

At least that's what it looks like using many variations of the same query on one of my tables. I'll provide a test case asap.
[1 Oct 2006 10:12] Valeriy Kravchuk
So, what about the test case that demonstrates the behaviour described (with JOINs), better - on 5.0.24a?
[2 Nov 2006 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".