Combination of JOIN and ORDER BY is slow
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.