Subqueries vs joins

Question

I refactored a slow section of an application we inherited from another company to use an inner join instead of a subquery like

where id in (select id from ... )

The refactored query runs about 100x faster. (~50 seconds to ~0.3) I expected an improvement, but can anyone explain why it was so drastic? The columns used in the where clause were all indexed. Does SQL execute the query in the where clause once per row or something?

Update - Explain results:

The difference is in the second part of the "where id in ()" query -

2   DEPENDENT SUBQUERY submission_tags ref st_tag_id st_tag_id 4 const 2966 Using where

vs 1 indexed row with the join:

    SIMPLE s eq_ref PRIMARY PRIMARY 4 newsladder_production.st.submission_id 1 Using index

Thanks everyone!

Answer 1

Run the explain-plan on each version, it will tell you why.

Answer 2

Look at the query plan for each query.

Where in and Join can typically be implemented using the same execution plan, so typically there is zero speed-up from changing between them.

Answer 3

You are running the subquery once for every row whereas the join happens on indexes.

Answer 4

Optimizer didn't do a very good job. Usually they can be transformed without any difference and the optimizer can do this.

Answer 5

With a subquery, you have to re-execute the 2nd SELECT for each result, and each execution typically returns 1 row.

With a join, the 2nd SELECT returns a lot more rows, but you only have to execute it once. The advantage is that now you can join on the results, and joining relations is what a database is supposed to be good at. For example, maybe the optimizer can spot how to take better advantage of an index now.

Answer 6

It isn't so much the subquery as the IN clause, although joins are at the foundation of at least Oracle's SQL engine and run extremely quickly.

Answer 7

The subquery was probably executing a "full table scan". In other words, not using the index and returning way too many rows that the Where from the main query were needing to filter out.

Just a guess without details of course but that's the common situation.

Answer 8

Usually its the result of the optimizer not being able to figure out that the subquery can be executed as a join in which case it executes the subquery for each record in the table rather then join the table in the subquery against the table you are querying. Some of the more "enterprisey" database are better at this, but they still miss it sometimes.

Answer 9

A "correlated subquery" (i.e., one in which the where condition depends on values obtained from the rows of the containing query) will execute once for each row. A non-correlated subquery (one in which the where condition is independent of the containing query) will execute once at the beginning. The SQL engine makes this distinction automatically.

But, yeah, explain-plan will give you the dirty details.

Answer 10

This question is somewhat general, so here's a general answer:

Basically, queries take longer when MySQL has tons of rows to sort through.

Do this:

Run an EXPLAIN on each of the queries (the JOIN'ed one, then the Subqueried one), and post the results here.

I think seeing the difference in MySQL's interpretation of those queries would be a learning experience for everyone.

Answer 11

before the queries are run against the dataset they are put through a query optimizer, the optimizer attempts to organize the query in such a fashion that it can remove as many tuples (rows) from the result set as quickly as it can. Often when you use subqueries (especially bad ones) the tuples can't be pruned out of the result set until the outer query starts to run.

With out seeing the the query its hard to say what was so bad about the original, but my guess would be it was something that the optimizer just couldn't make much better. Running 'explain' will show you the optimizers method for retrieving the data.

Answer 12

The where subquery has to run 1 query for each returned row. The inner join just has to run 1 query.

Answer 13

I agree on all saying that the subquery has to be run for each row whereas the inner join will be run once. But still I have seen situation in real-life where the optimizer will convert a subquery to an inner join.. Why didn't it do it in this case?

Answer 14

Here's an example of how subqueries are evaluated in MySQL 6.0.

The new optimizer will convert this kind of subqueries into joins.

Answer 15

Sorry to be a MySQL basher and not being able to offer any constructive advice, but the query optimizer looks like total s*** if it can't handle simple joins/subqueries. Truly, Oracle had similar issues in 8i, but this was fixed almost 10 years ago.