There is a "lastpost" index on the mybb_threads table which is defined as (lastpost, fid). Shouldn't it be in the reverse order (fid, lastpost)? Anyway adding such index (fid, lastpost) visibly helps in forumdisplay.php performance.
Does it really matter? could you explain more?
Not all of us professional MySQLers...
The following query is run by forumdisplay.php:
SELECT COUNT(t.tid) AS unread_count FROM mybb_threads t LEFT JOIN mybb_threadsread tr ON (tr.tid=t.tid AND tr.uid='1') LEFT JOIN mybb_forumsread fr ON (fr.fid=t.fid AND fr.uid='1') WHERE t.visible=1 AND t.closed NOT LIKE 'moved|%' AND t.fid IN (3) AND t.lastpost > IFNULL(tr.dateline,1218739891) AND t.lastpost > IFNULL(fr.dateline,1218739891) AND t.lastpost>1218739891
and it's relatively slow in my case (30k posts and 100-150 users online). After adding this index it's used by MySQL. You can check it by MySQL EXPLAIN command or simply by adding ?debug=1 to forumdisplay.php URL. In my case 20 rows are scanned by this query instead of about 700. I have no idea where the original index (lastpost, fid) can be usefull. It only helps to find the last post(s) in *all* forums and subforums. So if you have for example the sidebar box with last answered topics in all forums it will be used there but in normal forum pages not.
The order of index columns are very important and (lastpost, fid) is completely different from (fid, lastpost) for db engine. The index (lastpost, fid) helps to answer the question "on what forum the last post was done on 2008-08-21 15:45:13". The second one (fid, lastpost) helps to answer the question "what was the time of last post on forum with ID = 61" (what was the reason this index was created for, in my opinion).