Latest Posts/Threads - optimal query?

[stryka]

I have 3million posts in my DB... Any suggestions on how i should do the query for "latest threads"

I currently have the following

SELECT a.title, a.lastpost, a.firstpostid, a.replycount, a.postuserid, a.forumid, a.postusername, b.title as forumname FROM vb_thread as a LEFT JOIN vb_forum as b ON a.forumid = b.forumid WHERE a.forumid != '24' ORDER BY a.threadid DESC LIMIT 7

I was actually thinking of creating a cron job that runs the above (Hourly), inserts into a temp talbe and i just pull from the sing table when i do the real query call

[Deceptor]

Your idea for the cron job is probably a better solution. It will suffer from lag but you could avoid that by creating several hooks to update the "cache" when threads/posts are created/moderated/moved/deleted (soft/hard)/merged/etc..

[Andreas]

1) Why to you join the forum table?
You don't really need that as you can get the forum title from forum cache.
In theory the join is correct, but what is theoretetically correct is not necessarily the best thing to do performance wise ;)

2) If you want the most up-to-date data with minimal db load, i'd add a hook on threaddata_postsave to invalidate the cache when the thread status (visible, forumid) changes.
When you display the results, check if the cache is there if not: build it.