Hi,
SQLTuner script on my VPS shows that many queries are being executed without indexes. There also seem to be some slow queries. I have tried playing with adding some indexes without much success. I am hoping the DB experts can suggest proper indexes for some of these most frequent queries. Thanks in advance.
# Query_time: 26 Lock_time: 0 Rows_sent: 255 Rows_examined: 10226
SELECT m.id_msg
FROM f_messages AS m
INNER JOIN f_boards AS b ON (b.id_board = m.id_board)
INNER JOIN f_topics AS t ON (t.id_topic = m.id_topic)
WHERE (FIND_IN_SET(-1, b.member_groups) != 0)
AND b.id_board != 13
AND m.id_msg <= b.id_last_msg AND m.id_msg >= 7226
AND m.approved = 1
ORDER BY m.id_msg DESC
LIMIT 255;
What is the result of an "explain extended <your query>".
Knowing SMF internals, i see no optimization possible for this query if you kept the standard indexes. Adding "approved" to a covering index won't be worth it since it's not discriminating enough (approved will be 1 in all cases except a few ones and than only if you activated the approval system).
Thanks for the response, butch2k. Here is the output:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t index PRIMARY is_sticky 1
763 Using index; Using temporary; Using filesort
1 SIMPLE m ref PRIMARY,topic,id_board,approved,id_topic,current_topic current_topic 3 fdb.t.id_topic 9 Using where
1 SIMPLE b eq_ref PRIMARY PRIMARY 2 fdb.m.id_board 1 Using where
# Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 6
SELECT bi.id_ban, bi.email_address, bi.id_member, bg.cannot_access, bg.cannot_register,
bg.cannot_post, bg.cannot_login, bg.reason, IFNULL(bg.expire_time, 0) AS expire_time
FROM f_ban_items AS bi
INNER JOIN f_ban_groups AS bg ON (bg.id_ban_group = bi.id_ban_group AND (bg.expire_time IS NULL OR bg.expire_time > 1358426900))
WHERE
(((119 BETWEEN bi.ip_low1 AND bi.ip_high1)
AND (235 BETWEEN bi.ip_low2 AND bi.ip_high2)
AND (54 BETWEEN bi.ip_low3 AND bi.ip_high3)
AND (120 BETWEEN bi.ip_low4 AND bi.ip_high4)) OR ('119.235.54.120' LIKE bi.hostname) OR ((119 BETWEEN bi.ip_low1 AND bi.ip_high1)
AND (235 BETWEEN bi.ip_low2 AND bi.ip_high2)
AND (54 BETWEEN bi.ip_low3 AND bi.ip_high3)
AND (120 BETWEEN bi.ip_low4 AND bi.ip_high4)) OR ('119.235.54.120' LIKE bi.hostname));
Explain output:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE bg ALL PRIMARY 6 Using where
1 SIMPLE bi ALL id_ban_group 9 Using where
This table is so small that it's faster to query directly the table w/o resorting to indexes. If you kept SMF indexes and did not install any mods you should be good with the default indexes.
So no changes for query 1 or 2?
Query 1 seems to take a long time.
It's definitely not index related. What kind of hosting is it, shared/VPS ?
VPS Xen 512MB RAM
Pretty solid on load tests.
Do you notice any real slowdown on your site ?
Not really. Thanks.