Search code examples
mysqlsqlsqlperformance

Where in sub-query or in main query, what is preferable?

Simplified example 1:

SELECT * FROM (
    SELECT x, y, z FROM table1
    WHERE x = 'test'
    -- union, etc, etc, complicated stuff...
) AS t
-- union, etc, etc, complicated stuff...

Simplified example 2:

SELECT * FROM (
    SELECT x, y, z FROM table1
    -- union, etc, etc, complicated stuff...
) AS t
-- union, etc, etc, complicated stuff...
WHERE x = 'test'

Which of the above is more popular? Is more performant? Is recommended for other reasons? Does it help to filter the results "early", before doing union and similar operations? Thanks.

Solution

  • In MySQL you definitely want the filtering condition in the subquery. MySQL materializes subqueries. The smaller the subquery the faster the query.

    In addition, MySQL may be able to use an index for the condition.