Re: Query optimizer-miss with unqualified expressions, bug or feature?
- Date: Mon, 19 Oct 2015 15:19:44 +0200
- From: Roy Lyseng <roy.lyseng@xxxxxxxxxx>
- Subject: Re: Query optimizer-miss with unqualified expressions, bug or feature?
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.
CREATE TABLE t (
i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
a BOOLEAN NOT NULL,
KEY a (a)
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
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.
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql