frosas.net

Yesterday I was writing some SQL queries involving subqueries for a MySQL database when I saw it were being executed very poorly. This is an example of how it looked:

SELECT * FROM country WHERE id IN (
    SELECT country_id FROM province WHERE id = 123
)

Nothing complicated over here but the database could not deduce the inner query was independent from outer one and executed it for every result from outer query (according to EXPLAIN it was treated as a "dependent subquery").

One solution to this is to rewrite it using joins:

SELECT DISTINCT c.*
FROM
    country AS c
    INNER JOIN province AS p ON p.country_id = c.id
WHERE p.id = 123

But query becomes harder to both read and write.

Luckily it is possible to force the database engine to interpret inner query as independent by injecting a dummy SELECT between both queries:

SELECT * FROM country WHERE id IN (SELECT * FROM (
    SELECT country_id FROM province WHERE id = 123
) AS foo)

I've found this solution at SitePoint forums (thanks "John P.") and it seems it already works as expected in version 6.0 (currently in alpha state).