Advertisement:

Author Topic: Adding indexes for SMF performance?  (Read 4210 times)

Offline Glasso

  • Semi-Newbie
  • *
  • Posts: 77
  • Gender: Male
Adding indexes for SMF performance?
« on: January 17, 2013, 03:18:41 AM »
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.

Offline Glasso

  • Semi-Newbie
  • *
  • Posts: 77
  • Gender: Male
1st query - gets repeated often
« Reply #1 on: January 17, 2013, 03:21:19 AM »
Code: [Select]
# 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;

Offline butch2k

  • Full Member
  • ***
  • Posts: 418
  • Gender: Male
    • @butch2k on Twitter
    • Planète Roliste
Re: Adding indexes for SMF performance?
« Reply #2 on: January 17, 2013, 04:14:33 AM »
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).

Offline Glasso

  • Semi-Newbie
  • *
  • Posts: 77
  • Gender: Male
Query 1 - explain output
« Reply #3 on: January 17, 2013, 07:17:33 AM »
Thanks for the response, butch2k. Here is the output:

Code: [Select]
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
« Last Edit: January 17, 2013, 07:58:25 AM by Glasso »

Offline Glasso

  • Semi-Newbie
  • *
  • Posts: 77
  • Gender: Male
Query 2
« Reply #4 on: January 17, 2013, 08:00:18 AM »
Code: [Select]
# 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]
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
« Last Edit: January 18, 2013, 12:02:41 AM by Glasso »

Offline butch2k

  • Full Member
  • ***
  • Posts: 418
  • Gender: Male
    • @butch2k on Twitter
    • Planète Roliste
Re: Adding indexes for SMF performance?
« Reply #5 on: January 18, 2013, 03:52:37 AM »
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.

Offline Glasso

  • Semi-Newbie
  • *
  • Posts: 77
  • Gender: Male
Re: Adding indexes for SMF performance?
« Reply #6 on: January 18, 2013, 08:41:28 AM »
So no changes for query 1 or 2?
Query 1 seems to take a long time.

Offline butch2k

  • Full Member
  • ***
  • Posts: 418
  • Gender: Male
    • @butch2k on Twitter
    • Planète Roliste
Re: Adding indexes for SMF performance?
« Reply #7 on: January 18, 2013, 10:10:47 AM »
It's definitely not index related. What kind of hosting is it, shared/VPS ?

Offline Glasso

  • Semi-Newbie
  • *
  • Posts: 77
  • Gender: Male
Re: Adding indexes for SMF performance?
« Reply #8 on: January 18, 2013, 10:17:24 AM »
VPS Xen 512MB RAM
Pretty solid on load tests.

Offline butch2k

  • Full Member
  • ***
  • Posts: 418
  • Gender: Male
    • @butch2k on Twitter
    • Planète Roliste
Re: Adding indexes for SMF performance?
« Reply #9 on: January 18, 2013, 04:44:42 PM »
Do you notice any real slowdown on your site ?


Offline Glasso

  • Semi-Newbie
  • *
  • Posts: 77
  • Gender: Male
Re: Adding indexes for SMF performance?
« Reply #10 on: January 18, 2013, 09:15:46 PM »
Not really. Thanks.