Combination of JOIN and ORDER BY is slow

osvaldas 2016-04-16 10:40:25

There are two tables: posts (~5,000,000 rows) and relations (~8,000 rows).

posts columns:

-------------------------------------------
|  id  |  source_id  |  content  |  date  |
-------------------------------------------

relations columns:

---------------------------
|  source_id  |  user_id  |
---------------------------

I wrote a MySQL query for getting 10 most recent rows from posts which are related to a user:

SELECT      p.id, p.content
FROM        posts AS p
LEFT JOIN   relations AS r
ON          r.source_id = p.source_id
WHERE       r.user_id = 1
ORDER BY    p.date DESC
LIMIT       10

However, it takes ~30 seconds to execute it.

I already have indexes at relations for (source_id, user_id), (user_id) and for (source_id), (date), (date, source_id) at posts.

How can I optimize the query? Thanks.

Aaron Bertrand 2016-04-16 11:17:56
Hi osvaldas, we focus on SQL Server here; I think you will get better help over at dba.stackexchange.com.
April 16, 2016
No Comments