I am unclear why the forum_post_text has a non-unique index on post_id. Can there be more than one version of the post's text stored for each post? if not, then it would be beneficial to remove the post_text_id PRIMARY KEY column and make the PRIMARY KEY on the post_id column. This would speed up joins on this table considerably because MySQL could use a const eq_ref join from the forum_post to the forum_post_text table.
Also, there is a small normalization problem in the forum_post table. The field "author_is_moderator" is an attribute that describes the *user* not the *post* and therefore, belongs in either the user table or a table like "user2forum" if you enable users to be moderators of only one or more forums, not all or none.
Also, the following query:
SELECT
t.*, u.username AS username, u.id, u2.username as last_username, u2.id as last_user_id, p.post_nickname,
p.post_author_is_moderator, p.post_moved_board_id, p2.post_nickname AS last_nickname,
p2.post_author_is_moderator AS last_post_is_moderator,p2.post_date_posted AS last_post_date
FROM
forum_post p,forum_post p2,forum_thread t,user u,user u2
WHERE
t.thread_board_id = '38'
AND t.thread_user_id = u.id
AND p.post_id = t.thread_first_post_id
AND p2.post_id = t.thread_last_post_id
AND u2.id = p2.post_user_id
ORDER BY
t.thread_date_posted DESC
LIMIT 0,25
in using an inefficient index (on thread_board_id). It is returning too many rows (4132) in the first resultset, that is then filtered and joined to the other tables. You would probably get much more performance if you removed the thread_first_post, thread_last_post, and made an index on forum_thread (thread_board_id, thread_date_posted). Because you are ordering by thread_date_posted and filtering on board_id, this index should be able to reduce the number of initial rows returned significantly, which will increase the performance of the query. Let us know how these changes (if you decide to use them of course) work out.
Cheers,
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com