List:General Discussion« Previous MessageNext Message »
From:Markus Fischer Date:December 30 2007 6:48pm
Subject:Performance problem with ~0.5 GB tabel
View as plain text  
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
Thread
Performance problem with ~0.5 GB tabelMarkus Fischer30 Dec
  • Re: Performance problem with ~0.5 GB tabelMarkus Fischer3 Jan