Web lists-archives.com

Re: Query optimizer-miss with unqualified expressions, bug or feature?




Hi Roy,

Thanks for the clear explanation.

I guess (hypothetically) the optimizer could see if it has a key, and then use two starts: one on 'a > 0' and one on 'a < 0', taking a union of the result? Which might make a significant result to something?

Ben.


On 2015-10-19 14:19, Roy Lyseng wrote:
Hi Ben,

On 19.10.15 15.10, Ben Clewett wrote:

I have noticed that an unqualified boolean expression cannot be optimized by
MySQL to use an index in 5.6.24.

For example:

CREATE TABLE t (
   i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   a BOOLEAN NOT NULL,
   KEY a (a)
) ENGINE=InnoDB;

This will hit key 'a':

SELECT * FROM t WHERE a = TRUE;

This will return the same results, but not use key 'a':

SELECT * FROM t WHERE a;

Is this a bug, or deliberate behaviour, or a missing feature, or perhaps
something else?

MySQL does not have a true boolean type, so this is actually interpreted as

  SELECT * FROM t WHERE a <> 0;

The optimizer is not able to see that "a <> 0" means "a = 1", and hence no index will be used.

Thanks,
Roy


Thanks,

Ben Clewett.






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql