Hi Ben,
On 19.10.15 16.07, Ben Clewett wrote:
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?
That is correct. However, if the substitution type for BOOLEAN was UNSIGNED
TINYINT instead of TINYINT, the range 'a < 0' would not even be needed, and the
quite cumbersome UNION would be avoided. But the best solution would of course
be a two-valued boolean type, where 'a <> 0' would easily be transformed to a =
1. It would also mean that statistics for the columns would be better, with
TINYINT each value has the estimated probability 1/256, whereas a boolean value
would have probability 1/2.
Thanks,
Roy
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
