Improve SQL Query performance with JOIN

Question

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.

What do you mean by slow? How many long? How many rows do you have? — Cyril Gandon
– Cyril Gandon, Commented Apr 22, 2013 at 12:57
Along with scorps Q. How many columns are in table news-events. — Adam Gent
– Adam Gent, Commented Apr 22, 2013 at 12:59
the limit + order by desc statements are likely to turn performances down. If it is not mandatory don't order, or order by ASC. — Sebas
– Sebas, Commented Apr 22, 2013 at 13:01
@BrankoDimitrijevic It's usually used so you can comment out parts of the where clause more easily without having to rewrite parts of it. — fancyPants
– fancyPants, Commented Apr 22, 2013 at 13:02

Cyril Gandon · Accepted Answer · 2013-04-22 13:34:29Z

3

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

edited Apr 22, 2013 at 13:34

Cyril Gandon

17.1k15 gold badges84 silver badges124 bronze badges

answered Apr 22, 2013 at 13:00

Pradeep Pati

5,9293 gold badges31 silver badges43 bronze badges

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

2 Comments

Sebas Over a year ago

limit? sort desc? that's not the same query

fdezjose Over a year ago

Great solution! This improves a lot the performance of the query. Thank you.

Devart · Accepted Answer · 2013-04-22 13:32:53Z

1

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.

answered Apr 22, 2013 at 13:32

Devart

120k23 gold badges172 silver badges189 bronze badges

Comments

vivek · Accepted Answer · 2013-04-23 06:53:21Z

0

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!

answered Apr 23, 2013 at 6:53

vivek

114 bronze badges

Collectives™ on Stack Overflow

Improve SQL Query performance with JOIN

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related