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, Apr 22 '13 at 12:57
Along with scorps Q. How many columns are in table news-events. — Adam Gent, Apr 22 '13 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, Apr 22 '13 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, Apr 22 '13 at 13:02
I don't much about mysql explain, but it looks like "it" decided that it was better to fetch all news_events, and then match them with likes rows... On some DBMS, I was able to prevent this by using `a.id = b.content_id + 0`... — pascal, Apr 22 '13 at 13:04
Have you tried running the query with [read uncommitted](http://stackoverflow.com/a/918092/1262527) isolation level?..I think that may make it run faster — Mariam K., Apr 22 '13 at 13:47

score 3 · Accepted Answer · edited Apr 22 '13 at 13:34

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 '13 at 13:34

Cyril Gandon

16,830
14
78
122

answered Apr 22 '13 at 13:00

Pradeep Pati

5,779
3
29
43

limit? sort desc? that's not the same query – Sebas Apr 22 '13 at 13:03
Great solution! This improves a lot the performance of the query. Thank you. – fdezjose Apr 22 '13 at 15:02

score 1 · Answer 2 · answered Apr 22 '13 at 13:32

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.

score 0 · Answer 3 · answered Apr 23 '13 at 06:53

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!

Improve SQL Query performance with JOIN

3 Answers3