CODEWITHSUNDEEP
mysqlperformance
David Rodrigues
David Rodrigues

Reputation: 12542

Do where in all subqueries or on main query?

Should I do a WHERE in each subquery, or just on main query? My question is about performance. For instance:

WHERE in all subqueries:

SELECT ...
FROM ...
WHERE id_user = :id_user

UNION ALL
SELECT ...
FROM ...
WHERE id_user = :id_user

In this case, each subquery will have the same WHERE clause, filtering by id_user.

WHERE only on main query:

SELECT 
    SUBQUERY.*

FROM (
    SELECT ..., id_user
    FROM ...

    UNION ALL
    SELECT ..., id_user
    FROM ...
) AS SUBQUERY

WHERE SUBQUERY.id_user = :id_user

In this case, each subquery will return the id_user, so main query will get it and will filter.

In my mind, the first one will be faster, because it'll filter first, union, and return all. In second case, it'll find all, union, and filter. The advantage of second method is that I'll write the WHEREa single time -- imagine that I have 10 similar subqueries.

But the question is: will MySQL be capable to optimize it?

Upvotes: 3

Views: 618

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

MySQL materializes subqueries. That means that the subqueries are fully processed, before subsequent processing takes place.

So, in MySQL, you should put the where clause in the subqueries, to reduce the volume of data and speed the query. Your first version should have better performance than the second.

Also, you can remove the outer query for the first version. Just do:

(SELECT ...
 FROM ...
 WHERE id_user = :id_user
) UNION ALL
(SELECT ...
 FROM ...
 WHERE id_user = :id_user
)

This is not true in most other databases.

Upvotes: 4

Related Questions