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.
Just make a simple join. Sub-queries does not provide the best result quite often
EXPLAIN SELECT l.id, l.level_name, l.date_published, l.rating
FROM levels AS l
INNER JOIN users_favorites AS uf
ON uf.level_id = l.id
WHERE l.user_id = 2;
Best Answer
So I would have to look at the query optimizer source code of why it is printing range, but my guess is that for writes it will just use a limited number of write methods for simplification/limitation of EXPLAIN. If we look at the actual low row level operations of both queries, we can see that the
SELECT:... does a single row read through and index. And for the update:
... it uses the exact same read strategy + the required write to update the record.
So there is no overhead in terms of reading more records than necessary or any difference in terms of more expensive read plans. Whether it is a simplification for
EXPLAIN, or a limitation due to write queries, where it cannot go too far as to tell; or it shows a real difference because how writes work, and marks a real performance impact, one would need to go into implementation details- but for most cases, as long as the rows column says "1", I don't see any problems with the query- a range of 1 row should be almost equivalent to a const access, broadly speaking.