29

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?

Alireza
  • 3,676
  • 10
  • 38
  • 44

4 Answers4

23

You are getting the message

Impossible WHERE noticed after reading const tables

This is documented in the page you already linked.

MySQL has read all const (and system) tables and notice that the WHERE clause is always false

const tables are defined as

The table has at most one matching row, which is read at the start of the query. ... const is used when you compare all parts of a PRIMARY KEY or UNIQUE index to constant values.

You have a UNIQUE KEY on (fromid,toid). The query on WHERE 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

SELECT rid
FROM   relationship
WHERE  fromid = 60
       AND toid = 78
       AND is_approved = 's'
        OR is_approved = 'f'
        OR is_approved = 't'

AND has a higher precedence than Or, so this is the same as 

SELECT rid
FROM   relationship
WHERE  ( ( fromid = 60 ) AND ( toid = 78 ) AND ( is_approved = 's' ) )
        OR ( is_approved = 'f' )
        OR ( is_approved = 't' )

This 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.

Martin Smith
  • 87,941
  • 15
  • 255
  • 354
5

MySql Explain uses the values you provide, literally, to traverse rows of the associated tables. If you provide a constant/key value which is not in the associated table, MySql Explain will stop with this error. Simply query the associated table(s) for values which do exist and provide those in your Explain query and everything will work as expected.

grwww
  • 51
  • 1
  • 1
3

Impossible WHERE noticed after reading const tables in explain query?

This error occurs due to invalid value being put on a column which are either primary key or unique key.

Try with a correct value in the where clause .

András Váczi
  • 31,778
  • 13
  • 102
  • 151
Rjnish Kumar
  • 51
  • 2
0

I'm jumping into this late. But here is what I noticed for me.

I was doing this query and the item column was UNIQUE.

SELECT `vari-groupid` FROM shop_item_variations_group where `item` = 'itemnu1' limit 1

which would get the Impossible WHERE noticed after reading const tables

All I had to do was change "=" to "like" and it now is using my index.

SELECT `vari-groupid` FROM shop_item_variations_group where `item` like 'itemnu1' limit 1
Richard Dev
  • 101