Tips regarding the three tables and their indexes:
- A new combined index
(threadid, dateline)onpostmight help. The first part should help while joining, while the second part eases the sorting.
Right now only the index (dateline) is used to avoid filesort, but the whole index has to be scanned (see the first row of explain, which says type=index). By the way: There are two identical indexes (dateline) on post right now. One can be deleted.
You could extend the index on
post_thanks. Right now it only contains the column(postid), but your query restricts the result set byuserid. So adding this column as the first part of the existing index should help too ->(userid, postid).For the table
threadthe index over(threadid)is used. Since your query restricts the result set by specifying valid values forforumid, it might help to add a new index(threadid, forumid). Again the first part should help to join while the second part should help to find matching rows.
Attention: Actually the last two indexes might have to be reversed, if the first change proposal is applied! Right now the optimizer decided that it is faster to scan (the index of) post completely and then join in data from the other two tables. If there exists a proper index on post, it might instead be faster to first select only those rows of post_thanks and/or thread, which are relevant, and then join the post table in. Since the join is then applied after selecting rows, the index tips 2. and 3. should contain first the column which restricts the selection and second the column which helps joining.
If you have applied some (or all) changes, you might update your posting with the current output of EXPLAIN. Maybe it shows further space for improvement.
I would start by applying change 1. and take a look at EXPLAIN again to decide how to go on.
