CODEWITHSUNDEEP
mysqlsqlinner-join
Okan Kocyigit
Okan Kocyigit

Reputation: 13421

inner join and where in() clause performance?

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

Upvotes: 9

Views: 21214

Answers (3)

Transact Charlie
Transact Charlie

Reputation: 2203

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.....

Upvotes: -3

xkeshav
xkeshav

Reputation: 54022


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

Upvotes: 5

Stephen Chung
Stephen Chung

Reputation: 14605

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.

Upvotes: 15

Related Questions