0

I have a query to get data of friends of user. I have 3 tables, one is user table, second is a user_friend table which has user_id and friend_id (both are foreign key to user table) and 3rd table is feed table which has user_id and feed content. Feed can be shown to friends. I can query in two ways either by join or by using IN clause (I can get all the friends' ids by graph database which I am using for networking).

Here are two queries:

SELECT
  a.*
FROM feed a
INNER JOIN user_friend b ON a.user_id = b.friend_id
WHERE b.user_id = 1;

In this query I get friend ids from graph database and will pass to this query:

SELECT
 a.*
FROM feed a
WHERE a.user_id IN (2,3,4,5)

Which query runs faster and good for performance when I have millions of records?

Michel de Ruiter
  • 7,131
  • 5
  • 49
  • 74
Nikesh Kedlaya
  • 652
  • 4
  • 10
  • 30
  • Possible duplicate of [inner join and where in() clause performance?](https://stackoverflow.com/questions/5274618/inner-join-and-where-in-clause-performance) – User123456 Dec 28 '17 at 07:21
  • That question has sub query, i have actual value in IN Clause. I don't need to query a table to get values. But i have avg of 1000 values inside IN Clause. – Nikesh Kedlaya Dec 28 '17 at 07:46
  • They're two different queries. What's better; a hammer or a screwdriver? – Strawberry Dec 28 '17 at 09:13

2 Answers2

1

With suitable indexes, a one-query JOIN (Choice 1) will almost always run faster than a 2-query (Choice 2) algorithm.

To optimize Choice 1, b needs this composite index: INDEX(user_id, friend_id). Also, a needs an index (presumably the PRIMARY KEY?) starting with user_id.

Rick James
  • 135,179
  • 13
  • 127
  • 222
0

This depends on your desired result when you have a compared big data in your subquery their always a join is much preferred for such conditions. Because subqueries can be slower than LEFT [OUTER] JOINS / INNER JOIN [LEft JOIN is faster than INNER JOIN], but in my opinion, their strength is slightly higher readability.

So if your data have fewer data to compare then why you chose a complete table join so that depends on how much data you have.

In my opinion, if you have a less number of compared data in IN than it's good but if you have a subquery or big data then you must go for a join...

A.D.
  • 2,352
  • 2
  • 15
  • 25
  • I have actual value in IN Clause. I don't need to query a table to get values. But i have avg of 1000 values inside IN Clause. I don't have any sub query. – Nikesh Kedlaya Dec 28 '17 at 07:46
  • if this count equal to the number of records in the table then you can go for the `IN` otherwise use `JOIN` – A.D. Dec 28 '17 at 07:53