Mysql ENUM doesn't use index

Question

I've got a problem with enum field in MySQL. When i use select with only one value SELECT * FROM test WHERE t IN ('new') everything is fine, but in case of several values SELECT * FROM test WHERE t IN ('new','deleted') MySQL doesn't use indexes at all, so perfomance of this queries is very low. Is it possible to do something with that ?

CREATE TABLE `test` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`t` ENUM('new','active','deleted') NOT NULL,
PRIMARY KEY (`id`),
INDEX `t` (`t`)
)
ENGINE=MyISAM;


INSERT INTO test SET t = 'new';
INSERT INTO test SET t = 'new';
INSERT INTO test SET t = 'active';
INSERT INTO test SET t = 'active';
INSERT INTO test SET t = 'deleted';
INSERT INTO test SET t = 'deleted';


EXPLAIN SELECT * FROM test WHERE t IN ('new');
id|select_type|table|type|possible_keys|key|key_len|ref  |rows|Extra
1 |SIMPLE     |test |ref |t            |t  |1      |const|2   |Using index condition

EXPLAIN SELECT * FROM test WHERE t IN ('new','deleted');
id|select_type|table|type|possible_keys|key |key_len|ref |rows|Extra
1 |SIMPLE     |test |ALL |t            |NULL|NULL   |NULL|6    |Using where

Brian Hooper · Accepted Answer · 2017-07-26 00:47:08Z

1

The fact MySQL hasn't used the index in the second case doesn't mean the performance will be poor (although it might be). The differentiation on the index isn't very great, so the query optimiser takes the view that rather than scan the index twice (once for each value) and then read the rows for each selected index, it is quicker just to scan the table and pick out the rows directly.

Indexes like this will typically need a lot of TLC (Tender Loving Care) if they are to be useful. Have a look here for more.

edited Jul 26, 2017 at 0:47

user5890979

answered Jan 23, 2013 at 11:50

Brian Hooper

22.2k25 gold badges91 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mark Amery Over a year ago

What does TLC mean? All I can find is 'Transactions, Locking and Concurrency' but that doesn't seem to make sense in this context.

Brian Hooper Over a year ago

@MarkAmery, Tender Loving Care. The index has poor differentiation, so rebalancing will probably be necessary from time to time (the primary key would take care of itself).

Collectives™ on Stack Overflow

Mysql ENUM doesn't use index

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related