Adding indexes for SMF performance?

[Glasso]

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.

[Glasso]

Code Select Expand


# 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;


[butch2k]

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).

[Glasso]

Thanks for the response, butch2k. Here is the output:

Code Select Expand

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

[Glasso]

Code Select Expand

# 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:

Code Select Expand


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


[butch2k]

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.

[Glasso]

So no changes for query 1 or 2?
Query 1 seems to take a long time.

[butch2k]

It's definitely not index related. What kind of hosting is it, shared/VPS ?

[Glasso]

VPS Xen 512MB RAM
Pretty solid on load tests.

[butch2k]

Do you notice any real slowdown on your site ?

[Glasso]

Not really. Thanks.