Hi,
I'm using phorum [1] and made some custom queries against their
database. My query looks like this:
SELECT
message_id, subject, datestamp, forum_id, thread
FROM
phorum_messages
WHERE
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
manually helps.
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:
id: 1
select_type: SIMPLE
table: phorum_messages
type: range
possible_keys: thread_message, thread_forum, status_forum,
list_page_float, list_page_flat, dup_check,
last_post_time, forum_max_message, post_count
key: post_count
key_len: 9
ref: NULL
rows: 1311
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 [2] 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?
thanks,
- Markus
[1] http://www.phorum.org/
[2] http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
