Optimizing Inner Join Queries

I have this query and i want to know if i can optimize it in some way because currently it takes a long time to execute (like 4/5 seconds)

SELECT *
FROM `posts` ml INNER JOIN
posts_tag_one gt
ON gt.post_id = ml.id AND gt.tag_id = 15 INNER JOIN
posts_tag_two gg
ON gg.post_id = ml.id AND gg.tag_id = 5
WHERE active = '1' AND NOT ml.id = '639474'
ORDER BY ml.id DESC
LIMIT 5


I want to say the database it has like 600k+ posts, the posts_tag_one 5 milions records, the posts_tag_two 475k+ records.

That example i gave it's only with 2 joins but in some cases i have up to 4 joins so the other tables has like 300k-400k records.

I am using foregin keys and indexes for posts_tag_one, posts_tag_two tables but the query it's still slow.

Any advice would help. Thanks!

Continue reading...