Does the MySQL IN clause execute the subquery multiple times?

Question

Given this SQL query in MySQL:

SELECT * FROM tableA WHERE tableA.id IN (SELECT id FROM tableB);

Does MySQL execute the subquery SELECT id FROM tableB multiple times for each row in tableA?

Is there a way to make sql go faster without using variables or store procedures?

Why is this often slower than using LEFT JOIN?

Answer 1

Your assumption is false; the subquery will be executed only once. The reason why it's slower than a join is because IN can't take advantage of indexes; it has to scan its arguments once for each time the WHERE clause is evaluated, that is, once per row in tableA. You can optimize the query, without using variables or stored procedures, simply by replacing the IN with a join, thus:

SELECT tableA.field1, tableA.field2, [...]
FROM tableA 
  INNER JOIN tableB ON tableA.id = tableB.id

Unless you don't mind getting back every field from both tables, you do need to enumerate the fields you want in the SELECT clause; tableA.*, for example, will elicit a syntax error.

Answer 2

First, this depends on the version of MySQL. I believe that version 5.6 optimizes such queries correctly. MySQL documentation is inconsistent on this. For instance, here it says one thing:

Consider the following subquery comparison:

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

MySQL evaluates queries “from outside to inside.” That is, it first obtains the value of the outer expression outer_expr, and then runs the subquery and captures the rows that it produces.

This "from outside to inside" means that the subquery is evaluated for each row. This is consistent with my experience with MySQL.

The documentation suggests otherwise here:

Some optimizations that MySQL itself makes are:

• MySQL executes uncorrelated subqueries only once. Use EXPLAIN to make sure that a given subquery really is uncorrelated.
• MySQL rewrites IN, ALL, ANY, and SOME subqueries in an attempt to take advantage of the possibility that the select-list columns in the subquery are indexed.

I believe the statement does not refer to in clauses. Perhaps what happens is that the subquery is rewritten as a correlated subquery to check for indexes, and then it is run multiple times (regardless of the presence of a index).