CODEWITHSUNDEEP
mysqlsqlselectwhere-clause
Darith
Darith

Reputation: 9

MYSQL Explain Giving an “Impossible WHERE” with SELECT Count(x) Query

It seems I'm getting an "impossible WHERE" on a SELECT query. I've posted two queries below, which differentiate in the subquery. What these queries do is check to see if a user has saved something once before, before updating a count. I am using SELECT for testing purposes, but the actual query would be using UPDATE:

UPDATE articles SET article_count = article_count+1 
WHERE id = 2343243 AND (
    SELECT COUNT(*) 
    FROM posts as p
    WHERE p.post_id = 2343243 AND p.user_id = 3
) = 1;

The following two queries are what I'm using to test to see if the data is in the table (for testing only):

EXPLAIN 
SELECT a.id 
FROM articles as a 
WHERE a.id = 2343243 AND (
    SELECT COUNT(*) 
    FROM posts as p
    WHERE p.post_id = a.id AND p.user_id = 3
) = 1;

Query 1 returns Impossible WHERE in EXPLAIN. The select_type of the query #2 is SUBQUERY.

EXPLAIN 
SELECT a.id 
FROM articles as a 
WHERE a.id = 2343243 AND (
    SELECT COUNT(*) 
    FROM posts as p
    WHERE p.post_id = 2343243 AND p.user_id = 3
) = 1;

Query 2 returns :

Impossible WHERE noticed after reading const tables
in EXPLAIN.

The select_type of the Query 2 is DEPENDENT SUBQUERY.

Question: Any ideas on how to make this not an impossible WHERE query? And also, which would be faster?

Upvotes: 0

Views: 281

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562731

"Impossible where" means that your table does not include rows with the actual values you use in your EXPLAIN. In other words, in your data that is in the table right now, there is no row with p.post_id = 2343243 (or perhaps it applies to the articles.id).

So when using EXPLAIN, pick a different value that actually exists in the table.

+1 to Jacky Cheng for suggesting the multi-table UPDATE. It's likely to be much more efficient than either subquery or dependent subquery.

Upvotes: 0

Jacky Cheng
Jacky Cheng

Reputation: 1556

ug...better off not use subquery at all.

UPDATE articles a,posts p SET article_count = article_count+1 
WHERE a.id=p.post_id and a.id = 2343243 AND p.user_id=3;

Upvotes: 1

Related Questions