Bug #41064 enum query never use index and it should
Submitted: 27 Nov 2008 2:25 Modified: 27 Nov 2008 4:27
Reporter: Ivan Lopez Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:MySQL 5.0.27-standard, 5.0.70, 6.0.7 OS:Linux
Assigned to: Assigned Account CPU Architecture:Any
Tags: enum index

[27 Nov 2008 2:25] Ivan Lopez 
Description:
I'm trying to use a query using the indexes created for this, but it never take it... even if i try to force it.

How to repeat:
CREATE TABLE `tst` (
  `indice` int(10) unsigned NOT NULL auto_increment,
  `procesado` enum('n','s','a') default 'n',
  PRIMARY KEY  (`indice`),
  KEY `procesado` (`procesado`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

EXPLAIN SELECT * FROM `tst` WHERE `procesado`!='s'

id  	 select_type  	 table  	 type  	 possible_keys  	 key  	 key_len  	 ref  	 rows  	 Extra
1 	SIMPLE 	procesar_emails 	ALL 	procesado 	NULL 	NULL 	NULL 	221 	Using where
[27 Nov 2008 4:27] Valeriy Kravchuk 
Thank you for a problem report. Indeed, all versions of MySQL does not use index in this case, even when you try to FORCE it. This is because of != used and column having low cardinality.

In ideal world optimizer should re-write the query as

SELECT * FROM `tst` WHERE `procesado`='n' or `procesado`='a';

or 

SELECT * FROM `tst` WHERE `procesado`='n' 
UNION 
SELECT * FROM `tst` WHERE `procesado`='a';

in cases where value `s` is used in a few rows only. But this would be possible only when we will have histograms of data distribution and more advanced query re-write technics implemented, so this is a feature request.