1

In SQL, how much the order of JOIN vs WHERE affect the performance of a query?

a) SELECT […] FROM A JOIN ( SELECT […] FROM B WHERE CONDITION ) ON […]

b) SELECT […] FROM A JOIN ( SELECT […] FROM B ) ON […] WHERE CONDITION

My inner feeling tells me that option a) should be more performant: if we do first a join and then we run a where, it seems way less performant than first running a where on one table, and from the resutls doing a join. But I’m not sure as this depends on the internal optimizations of the SQL library itself.

Would be nice to know if the behavior is the same for both MySQL and PostgreSQL, and also if it depends on any other decorators as group by or order by.

vilanovi
  • 2,097
  • 2
  • 21
  • 25
  • 1
    The second query makes no sense. Why put that in a subquery? Just join against B – juergen d Apr 14 '20 at 06:57
  • 2
    Trust the optimizer, write SQL that is easy to understand. Compare execution plans when in doubt. – jarlh Apr 14 '20 at 06:58
  • Neither option looks particularly good – Strawberry Apr 14 '20 at 07:25
  • 1
    Does this answer your question? [In which sequence are queries and sub-queries executed by the SQL engine?](https://stackoverflow.com/questions/2263186/in-which-sequence-are-queries-and-sub-queries-executed-by-the-sql-engine) – philipxy Apr 14 '20 at 07:27

2 Answers2

1

All other factors being equal, I would expect the A version to perform better than the B version, as you also seem to expect. The main reason for this is that the A version lets the database throw out rows using the WHERE clause in the subquery. Then the join only has to involve a smaller intermediate table. The exact difference in performance between the two would depend on the underlying data and the actual queries. Note that it is even possible that both queries could be optimized under the hood to the same or very similar execution plan.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • 1
    These are trivially optimizable to the same implementation, and there is no justifiation for entertaining any particular execution model without reference to EXPLAIN output, documented optimizations or actual DBMS version impementation, and this answer just misleads. – philipxy Apr 14 '20 at 07:08
  • 1
    @philipxy Well this is all I can say without seeing the actual queries (and my answer gives this disclaimer). – Tim Biegeleisen Apr 14 '20 at 07:10
1

Postgres has a smart optimizer so the two versions should have similar execution plans, under most cases (I'll return to that in a moment).

MySQL has a tendency to materialize subqueries. Although this has gotten better in more recent versions, I still recommend avoiding it. Materializing subqueries prevents the use of indexes and can have a significant impact on performance.

One caveat: If the subquery is complicated, then it might be better to filter as part of the subquery. For instance, if it is an aggregation, then filtering before aggregating usually results in better performance. That said, Postgres is smart about pushing conditions into the subquery. So, if the outer filtering is on a key used in aggregation, Postgres is smart enough to push the condition into the subquery.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786