Hi all, I don't know if this question has a simple answer that I'm just foolishly missing but I figure I've got to ask it anyway.

I've got a phpBB that has a pretty large database, and I'm trying to write some code that pulls up the N latest posts (arround 50 out of arround 500,000)

The problem is that I want to pull from a group of forums and I end up with problems with speed. Basicaly it cripples the site when I have ~100+ concurrent users (on a 5 min page serve average)

Originaly I had this SQL

SELECT t.*, u.username, u.user_id, p.*
FROM (( " . TOPICS_TABLE . " t
LEFT JOIN " . POSTS_TABLE . " p ON t.topic_last_post_id = p.post_id )
LEFT JOIN " . USERS_TABLE . " u ON p.poster_id = u.user_id )
WHERE t.forum_id in ($display_latest)
ORDER BY p.post_time DESC
LIMIT 0, 50

and the explain is not a happy camper,

table type possible_keys key key_len ref rows Extra
t range forum_id,latest_posts latest_posts 2 NULL 36296 Using where; Using temporary; Using filesort
p eq_ref PRIMARY PRIMARY 3 t.topic_last_post_id 1
u eq_ref PRIMARY PRIMARY 3 p.poster_id 1

I can remove the Using temporary by altering the sorting order to sort by t.topic_last_post_id which happily has sequntial ID's but I can't get and index that will work, and I'm not sure how best to improve the performance of this query.

Thanks in advance (and feel free to mock my foolishness if I've missed something that's as plane as the nose on my face :-) )

Robert Laverick