MySQL Bugs: #21486: No index merge for ENUM columns

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:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.22	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any
Tags:	enum, INDEX, merge, Optimizer

Description:
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
) ENGINE = MYISAM ;

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:
DO SOMETHING! :-)

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.

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.

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.

So, what about the test case that demonstrates the behaviour described (with JOINs), better - on 5.0.24a?

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".