I'm using phorum  and made some custom queries against their
database. My query looks like this:
message_id, subject, datestamp, forum_id, thread
forum_id IN (1, 2, 3, 4) AND parent_id = 0 AND
thread != 0 AND status = 2 AND closed = 0
ORDER BY datestamp DESC LIMIT 3
The table phorum_message is about 500MB in size. The problem is that
such a query often starts to "hang" in the "Sorting result" phase. This
can take up to minutes and during this time problems really start: more
and more such queries are coming in, each of them "hanging" for the same
reason too and after a few minutes the maximum of connections are
reached (currently 170) and everything is dead. Only killing the queries
My guess is that the filesort is problematic and so I tried to avoid it
with the following things.
When I use explain on the query I get back the following:
possible_keys: thread_message, thread_forum, status_forum,
list_page_float, list_page_flat, dup_check,
last_post_time, forum_max_message, post_count
Extra: Using where; Using filesort
When I remove the ORDER BY statements, the query is *not* using
filesort. However, as you can guess, it is necessary. The goal of the
query is to get the top-most posters in the selected forums.
The MySQL documentation  says that under certain cases it should be
possible to create appropriate keys so that even an ORDER BY can take
advantage of, but I was unable to come up with such an.
Is there a recommendation how to go for it?