CODEWITHSUNDEEP
mysqlsqldatabaseperformance
fdezjose
fdezjose

Reputation: 617

Improve SQL Query performance with JOIN

I've got the following, slow performing, SQL query:

SELECT *
FROM news_events
WHERE 1 AND (user_id = 2416) OR id IN(SELECT content_id FROM likes WHERE user_id = 2416)
ORDER BY id DESC
LIMIT 0,10

The news_events table has indexes on user_id. And the likes table has an index on user_id.

To try to improve performance I have re-written the query using an INNER JOIN the following way:

SELECT a.*
FROM news_events a
INNER JOIN likes b ON (a.id = b.content_id)
WHERE (a.user_id = 2416) OR (b.user_id = 2416)
ORDER BY a.id DESC
LIMIT 0,10

But performance doesn't improve either. I've run explain on this last query and this is the result:

mysql explain

I appreciate any pointer on what I could do to improve the performance of this query.

Upvotes: 1

Views: 2477

Answers (3)

vivek
vivek

Reputation: 11

Your query is quite good enough. Posted queries by mates are also fine. But, if you are having large set of data and you did not rebuild indexes since long then, you need to rebuild indexes on both tables.

It is a standard protocol that db admin need to rebuild all the indexes timely as well as recompile all the objects+packages in the db.

I hope it will help :) Keep querying!

Upvotes: 0

Pradeep Pati
Pradeep Pati

Reputation: 5919

SELECT *
FROM 
(
    SELECT a.* 
    FROM news_events a 
    WHERE a.user_id = 2416
    UNION
    SELECT ne.* 
    FROM news_events ne 
        INNER JOIN likes l 
            ON ne.id=l.contentid 
    WHERE l.user_id = 2416
)
ORDER BY 1 DESC
LIMIT 0,10

Upvotes: 3

Devart
Devart

Reputation: 122042

Try this query -

SELECT * FROM news_events ne
LEFT JOIN (SELECT content_id FROM likes WHERE user_id = 2416) l
  ON ne.user_id = 2416 OR ne.id = l.content_id
ORDER BY
  ne.id DESC
LIMIT
  0, 10

These columns should be indexed: news_events.user_id, news_events.id, likes.user_id, likes.content_id.

Upvotes: 1

Related Questions