inner join and where in() clause performance?

Question

I can get same result for these queries, but which one is the fastest, and most efficient?

where in() or inner join?

SELECT `stats`.`userid`,`stats`.`sumpoint` 
FROM  `stats` 
INNER JOIN users
ON `stats`.`userid` = `users`.`userid` 
WHERE `users`.`nick` =  '$nick'

ORDER BY `statoylar`.`sumpoint` DESC  limit 0,10

and

SELECT `stats`.`userid`,`stats`.`sumpoint` 
FROM  `stats` 
WHERE userid
IN (
SELECT userid
FROM  `users` 
WHERE  `users`.`nick` =  '$nick'
)
ORDER BY `stats`.`sumpoint` DESC  limit 0,10

MySQL has problems with `in` and sub queries. See http://stackoverflow.com/questions/3417074/why-would-an-in-condition-be-slower-than-in-sql — Martin Smith, Mar 12 '11 at 01:14

score 13 · Accepted Answer · answered Mar 11 '11 at 15:15

Depends on your SQL engine. Newer SQL systems that have reasonable query optimizers will most likely rewrite both queries to the same plan. Typically, a sub-query (your second query) is rewritten using a join (the first query).

In simple SQL engines that may not have great query optimizers, the join should be faster because they may run sub-queries into a temporary in-memory table before running the outer query.

In some SQL engines that have limited memory footprint, however, the sub-query may be faster because it doesn't require joining -- which produces more data.

So, in summary, it depends.

score 4 · Answer 2 · answered Mar 11 '11 at 15:16

4

to check the performance execute both Query with EXPLAIN SELECT .... AFAIK, INNER JOIN is faster than IN
btw what is your type of table engine MYISAM or INNODB

answered Mar 11 '11 at 15:16

xkeshav

53,360
44
177
245

1

also one dependency factor is `index` – xkeshav Mar 11 '11 at 15:47
1

+1 for suggesting 'explain select', of which I'm surprised this is the only answer to suggest that. – Seaux Apr 08 '13 at 15:44

score -3 · Answer 3 · answered Mar 12 '11 at 00:37

also there is another option, EXISTS. I'm a tsql guy so....

SELECT s.[userid], s.[sumpoint] 
FROM stats AS s
WHERE
     EXISTS (
     SELECT 1
     FROM users AS u
     WHERE
         u.[userID] = s.[userID]
         AND u.[nick] = '$nick'
     )
ORDER BY s.[sumpoint] DESC

I think EXISTS is available in most engines. It's generally pretty fast.

IN sql server at least (2005+) there is no performance difference at all between IN and EXISTS for cases where the column in question is not NULLABLE.

probably irrelevant but hey.....

inner join and where in() clause performance?

3 Answers3

Linked

Related