I have multiple tables of a php board.
I need an effective query, to select all categories, all topics, last post of topics, with the posted user. With my query it takes 5-8 seconds to run it. I did an optimize with last_post_id field into topics table, but I need a better solution for it.
Structure
forum_categories ~ 15 lines
id|name|...
forum_topics ~ 150 lines
id|name|category_id|...
forum_posts ~ 1.000.000 lines
id|body|topic_id|user_id|...
users ~ 30.000 lines
id|username|...
category 1
- topic 1
- last post1 | user1
- topic 2
- last post2 | user2
...
category 2
- topic 3
- last post3 | user3
...
...
Last query (This was a help from my friend. But this also was so slow. )
SELECT c.NAME AS category,
t.NAME AS topic,
p.body AS post,
p.username AS username
FROM forum_categories AS c
JOIN forum_topics AS t
ON t.category_id = c.id
JOIN (SELECT *
FROM (SELECT p.body,
p.topic_id,
u.username
FROM forum_posts AS p
JOIN users AS u
ON u.id = p.user_id
ORDER BY p.id DESC) AS t
GROUP BY topic_id) AS p
ON t.id = p.topic_id
Exaplain query
Statistic of query
Headers are: sort, status, time | status, all time, pct. time, calls, time