CODEWITHSUNDEEP
mysqlsqlperformancejoinsubquery
fishbone
fishbone

Reputation: 3259

Is scalar subquery on where condition executed for each join in MySQL?

Given a MySQL-query which selects all houses with 5 windows:

SELECT * FROM house
WHERE 5 = (SELECT count(*) FROM window WHERE window.house_id = house.id)

Now we want to join all doors of those houses:

SELECT * FROM house
   LEFT JOIN door ON door.house_id = house.id
WHERE 5 = (SELECT count(*) FROM window WHERE window.house_id = house.id)

My question is whether the subquery in the where-condition is executed for each door. Is MySQL smart enough to realize that the subquery doesn't depend on the joined doors and therefore only has to be executed once per house?

Upvotes: 1

Views: 269

Answers (1)

user2858650
user2858650

Reputation:

What you have here is called a correlated subquery. The inner query:

SELECT count(*) FROM window...

will run once for every single matching row in the outer query. So in answer to your question whether MySQL smart enough to realize that the subquery doesn't depend on the joined doors and therefore only has to be executed once per house... the answer is no but it is not MySQL that drives this but rather the ANSI SQL standard.

There are places where correlated subqueries are valuable but often times when they are used it reveals an opportunity for optimization. Here are a couple of decent articles about optimizing correlated subqueries. They aren't all specifically geared toward MySQL but the concepts should be the same.

Upvotes: 1

Related Questions