Query causing copying to tmp table

[autogespot.com]

The following query is causing a copy to tmp table...
On my high traffic site... which performance is seriously degraded, because everyone is hitting F5 as soon the forum doesn't responde in a few seconds, which causes more and more delay (because of the table locks).

maybe I should recreate the indexes or something ?

SELECT
            b.id_board, b.name AS bname, c.id_cat, c.name AS cname, m.id_topic, m.id_msg,
            t.id_member_started, t.id_first_msg, t.id_last_msg, m.body, m.smileys_enabled,
            m.subject, m.poster_time
         FROM smf_messages AS m
            INNER JOIN smf_topics AS t ON (t.id_first_msg = m.id_msg)
            INNER JOIN smf_boards AS b ON (b.id_board = t.id_board)
            LEFT JOIN smf_categories AS c ON (c.id_cat = b.id_cat)
         WHERE m.id_member = 614
            AND (FIND_IN_SET(-1, b.member_groups))
         ORDER BY m.id_msg DESC
         LIMIT 0, 15;

[H]

Which SMF version are you running? There was a lot of work in RC2 to try and eliminate slow queries

/edit: I see one of our beta testers has just posted some more slow query logs and this may still be an issue even with RC2

[autogespot.com]

Results from the explain query;

1;"SIMPLE";"b";"ALL";"PRIMARY";NULL;NULL;NULL;50;"Using where; Using temporary; Using filesort"
1;"SIMPLE";"c";"eq_ref";"PRIMARY";"PRIMARY";"1";"admin_forum2.b.id_cat";1;""
1;"SIMPLE";"t";"ref";"firstMessage,ID_BOARD,last_message_sticky";"ID_BOARD";"2";"admin_forum2.b.id_board";156;""
1;"SIMPLE";"m";"eq_ref";"PRIMARY,ID_MEMBER,participation,showPosts";"PRIMARY";"4";"admin_forum2.t.id_first_msg";1;"Using where"

[Something like that]

This is because of the FIND_IN_SET function call in the WHERE side of the query. MySQL doesn't support indexes on functions (Oracle and PostgreSQL do though).

[autogespot.com]

Okay... but is there a quick fix for this problem?

[Something like that]

Unfortunately, no. The fix for issues like that won't be until 2.1.

I would suggest increasing these two values in my.cnf: tmp_table_size and max_heap_table_size and set them both to something like 64M.

Also, if you are using InnoDB, make sure that innodb_buffer_pool_size is set between 1/8th to 1/4th of your system RAM.

See if that helps.

[autogespot.com]

Okey...

Already done mysql variable tuning like that The default values are really way too low
Thanks anyway!

Think it can be closed..