MySQL Join/WHERE IN performance

Question

I'm running the following query to select all posts liked by a user. The problem is, it takes quite a few seconds for the page to load.

SELECT p.*, a.username, a.avatar FROM user_posts p
LEFT JOIN account a ON p.uid=a.id WHERE p.pid in
(select post from user_posts_likes where `by`='$user_id')
ORDER BY `pid` DESC LIMIT $npage, 10";

Is there a better way to do this instead of using WHERE IN?

Thanks.

Hard to say from here. Prefix the query with explain to get the plan, and add it to your question — Tony Hopkinson
– Tony Hopkinson, Commented Dec 28, 2013 at 22:27

Mchl · Accepted Answer · 2013-12-28 22:39:32Z

3

You can try two joins like this:

SELECT p.*, a.username, a.avatar FROM user_post_likes l
JOIN post p ON l.post = p.pid
LEFT JOIN account a ON p.uid = a.id
WHERE l.by = 555

I deducted the foreign key names from your original query so they might be wrong. The 555 is an example user id, obviously.

edited Dec 28, 2013 at 22:39

Mchl

62.4k9 gold badges121 silver badges121 bronze badges

answered Dec 28, 2013 at 22:34

lafor

12.8k4 gold badges34 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Join/WHERE IN performance

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related