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;
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
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"
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).
Okay... but is there a quick fix for this problem?
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.
Okey...
Already done mysql variable tuning like that :) The default values are really way too low ;)
Thanks anyway!
Think it can be closed..