CODEWITHSUNDEEP
mysql
Seeker
Seeker

Reputation: 365

mysql - "INNER JOIN" or "IN". Which is faster?

could you please tell me which one of these two sentences is faster?

1st sentence:

SELECT DISTINCT(t1.user_id) FROM table_users as t1 WHERE t1.user_town IN (SELECT t2.town FROM table_towns as t2)

2nd sentence:

SELECT DISTINCT(t1.user_id) FROM table_users as t1 
  INNER JOIN (SELECT t2.town FROM table_towns as t2) as t3 ON t1.user_town = t3.town

The Selects I use are usually a bit more complex. And when possible I try to use IN (...) because I can pass a value or a table select.

Upvotes: 4

Views: 2940

Answers (5)

a1ex07
a1ex07

Reputation: 37364

The 2 queries you posted may produce different results (second with JOIN can return more rows than the first), so I think it's not correct to compare performance.

Upvotes: 0

Icarus
Icarus

Reputation: 63956

There's no definite answer to your question. It really depends on what are the indexes on the tables and whether the DBMS you are using can use them or not. I've had instances where an IN reduces the execution time compared to INNER JOIN and instances where INNER JOIN is faster.

Anecdotal example (using SQL Server, though):

I was joining a temp table to a regular table using a common field. The execution plan showed a HASH JOIN between the two tables when I looked at the execution plan. Execution time was 2 secs aprox. I changed the INNER JOIN to IN with a subselect ,the HASH JOIN disappeared and the execution time was cut to 1 sec aprox. It was now using the index on the main table.

Upvotes: 0

GolezTrol
GolezTrol

Reputation: 116110

IN is killing for MySQL. Rather use EXISTS.

INNER JOIN can be pretty fast, but the results are not necessarily the same. You may need grouping or distinct to get the same (or similar) results, though in this case I think you're safe.

If using an inner join, don't make it a subselect, because MySQL doesn't handle those well either. Just join on the table_towns table itself.

Upvotes: 0

Álvaro González
Álvaro González

Reputation: 146450

They both look fairly similar to my eyes. I'm pretty sure that a single SELECT will provide better performance, given that MySQL is not particularly good with subqueries:

SELECT t1.*
FROM table_users t1 
INNER JOIN table_towns t2 ON t1.user_town = t2.town

In any case, you should see what the EXPLAIN plan for the query has to say and perform some benchmarking on live data.

Upvotes: 2

JB Nizet
JB Nizet

Reputation: 691735

You should measure it, and issue an explain plan to be sure. But I would use neither of those:

select u.* from table_users as u
inner join table_towns as t on u.user_town = t.town

Upvotes: 0

Related Questions