I have a unique compound key like fr(fromid,toid) in the table, when I run the query with explain I get the following result:
Impossible WHERE noticed after reading const tables`
The query I ran:
explain SELECT rid FROM relationship WHERE fromid=78 AND toid=60
Any help?
EDIT1:
When I use the below query:
explain SELECT rid FROM relationship WHERE fromid=60 and toid=78 AND is_approved='s' OR is_approved='f' OR is_approved='t'
I see USING WHERE
instead of the previous message, but when I use the below query:
explain SELECT rid FROM relationship WHERE fromid=60 and toid=78 AND (is_approved='s' OR is_approved='f' OR is_approved='t')
I again get the first impossible ...
message! What these parenthesis do here?
EDIT2:
CREATE TABLE `relationship` (
`rid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`fromid` mediumint(8) unsigned NOT NULL,
`toid` mediumint(8) unsigned NOT NULL,
`type` tinyint(3) unsigned NOT NULL,
`is_approved` char(1) NOT NULL,
PRIMARY KEY (`rid`),
UNIQUE KEY `fromid` (`fromid`,`toid`),
KEY `toid` (`toid`),
CONSTRAINT `relationship_ibfk_1` FOREIGN KEY (`fromid`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `relationship_ibfk_2` FOREIGN KEY (`toid`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
EDIT3:
As mysql site say:
Impossible WHERE noticed after reading const tables
MySQL has read all const (and system) tables and notice that the WHERE
clause is always false.
But in the query I get the result I want, the WHERE
part is not false
. Is there someone who could explain this and shed some light on the subject?
Best Answer
You are getting the message
This is documented in the page you already linked.
const
tables are defined asYou have a
UNIQUE KEY
on(fromid,toid)
. The query onWHERE fromid=78 AND toid=60
can be satisfied by reading this unique index. From the message you are getting this must return no results.Similarly the query
WHERE fromid=60 and toid=78 AND (is_approved='s' OR is_approved='f' OR is_approved='t')
can also use this index to locate the row of interest (though it still has a residual predicate to evaluate were any row to match).Your other query is different
AND
has a higher precedence thanOr
, so this is the same asThis can no longer use that index and has different semantics in that it will return any rows where
is_approved IN ('f','t')
irrespective of what the values in the other columns are.