Facebook  Twitter 

SMFHacks.com

+- +-

SMFHacks.com

+- User Information

Welcome, Guest.
Please login or register.
 
 
 
Forgot your password?

+- Forum Stats

Members
Total Members: 12172
Latest: LeaMoeller
New This Month: 14
New This Week: 1
New Today: 1
Stats
Total Posts: 39049
Total Topics: 6874
Most Online Today: 59
Most Online Ever: 2482
(April 09, 2011, 07:02:45 pm)
Users Online
Members: 1
Guests: 25
Total: 26

Author Topic: SQL Query Slow (Suggestions)  (Read 268 times)

0 Members and 1 Guest are viewing this topic.

Offline shuban

  • Hero Member
  • *****
  • Posts: 637
    • View Profile
    • Biology Forums - For All Your Science Needs
SQL Query Slow (Suggestions)
« on: July 09, 2018, 11:58:04 am »
I posted this on the main SMF forum, but didn't get a favorable response. Was hoping to get SMFHack's expertise.

Code: [Select]
SELECT
mso.subject AS first_subject, ml.ID_MSG, mso.ID_MSG AS id_msg_first, ml.posterTime AS first_poster_time, t.ID_TOPIC, t.ID_BOARD, b.name AS bname,
t.numReplies, t.numViews, t.ID_MEMBER_STARTED AS id_first_member, ml.ID_MEMBER AS id_last_member,
ml.posterTime AS last_poster_time, mso.posterName AS first_poster_name,
IFNULL(meml.realName, ml.posterName) AS last_poster_name, ml.subject AS last_subject,
ml.icon AS last_icon, mso.icon AS first_icon,t.ID_POLL, t.isSticky, t.locked, ml.modifiedTime AS last_modified_time,
CASE WHEN IFNULL(lt.ID_MSG, IFNULL(lmr.ID_MSG, 0)) < t.ID_LAST_MSG THEN 1 ELSE 0 END AS is_new, SUBSTRING(ml.body, 1, 385) AS last_body,
SUBSTRING(mso.body, 1, 385) AS first_body, ml.smileysEnabled AS last_smileys, mso.smileysEnabled AS first_smileys, t.ID_FIRST_MSG, t.ID_LAST_MSG
FROM smf_topics AS t
INNER JOIN smf_messages AS ml ON (ml.ID_MSG = t.ID_LAST_MSG)
INNER JOIN smf_messages AS mso ON (mso.ID_MSG = t.ID_FIRST_MSG)
LEFT JOIN smf_boards AS b ON (b.ID_BOARD = ml.ID_BOARD)
LEFT JOIN smf_members AS meml ON (meml.ID_MEMBER = ml.ID_MEMBER)
LEFT JOIN smf_log_topics AS lt ON (lt.ID_TOPIC = ml.ID_TOPIC AND lt.ID_MEMBER = 6)
LEFT JOIN smf_log_mark_read AS lmr ON (lmr.ID_BOARD = t.ID_BOARD AND lmr.ID_MEMBER = 6)
WHERE t.ID_BOARD IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 17, 20, 21, 22, 23, 24, 25, 33, 34, 35, 38, 41, 43, 48, 49, 50, 55, 59, 61, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 95, 96, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117)
AND t.ID_LAST_MSG >= 4708323 AND ml.posterTime > unix_timestamp(now() - interval 14 day)
ORDER BY t.ID_LAST_MSG DESC
LIMIT 0, 40

Sometimes takes a minute to load, what gives?

Offline SMFHacks

  • Administrator
  • Hero Member
  • *****
  • Posts: 14551
    • View Profile
Re: SQL Query Slow (Suggestions)
« Reply #1 on: July 09, 2018, 12:10:29 pm »
Can post the results of 
describe smf_messages;

And also run
Code: [Select]
explain SELECT
mso.subject AS first_subject, ml.ID_MSG, mso.ID_MSG AS id_msg_first, ml.posterTime AS first_poster_time, t.ID_TOPIC, t.ID_BOARD, b.name AS bname,
t.numReplies, t.numViews, t.ID_MEMBER_STARTED AS id_first_member, ml.ID_MEMBER AS id_last_member,
ml.posterTime AS last_poster_time, mso.posterName AS first_poster_name,
IFNULL(meml.realName, ml.posterName) AS last_poster_name, ml.subject AS last_subject,
ml.icon AS last_icon, mso.icon AS first_icon,t.ID_POLL, t.isSticky, t.locked, ml.modifiedTime AS last_modified_time,
CASE WHEN IFNULL(lt.ID_MSG, IFNULL(lmr.ID_MSG, 0)) < t.ID_LAST_MSG THEN 1 ELSE 0 END AS is_new, SUBSTRING(ml.body, 1, 385) AS last_body,
SUBSTRING(mso.body, 1, 385) AS first_body, ml.smileysEnabled AS last_smileys, mso.smileysEnabled AS first_smileys, t.ID_FIRST_MSG, t.ID_LAST_MSG
FROM smf_topics AS t
INNER JOIN smf_messages AS ml ON (ml.ID_MSG = t.ID_LAST_MSG)
INNER JOIN smf_messages AS mso ON (mso.ID_MSG = t.ID_FIRST_MSG)
LEFT JOIN smf_boards AS b ON (b.ID_BOARD = ml.ID_BOARD)
LEFT JOIN smf_members AS meml ON (meml.ID_MEMBER = ml.ID_MEMBER)
LEFT JOIN smf_log_topics AS lt ON (lt.ID_TOPIC = ml.ID_TOPIC AND lt.ID_MEMBER = 6)
LEFT JOIN smf_log_mark_read AS lmr ON (lmr.ID_BOARD = t.ID_BOARD AND lmr.ID_MEMBER = 6)
WHERE t.ID_BOARD IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 17, 20, 21, 22, 23, 24, 25, 33, 34, 35, 38, 41, 43, 48, 49, 50, 55, 59, 61, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 95, 96, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117)
AND t.ID_LAST_MSG >= 4708323 AND ml.posterTime > unix_timestamp(now() - interval 14 day)
ORDER BY t.ID_LAST_MSG DESC
LIMIT 0, 40;

Get your Forum Ranked! at https://www.forumrankings.net - find out how your forum compares with others!

Like What I do? Support me at https://www.patreon.com/vbgamer45/

Offline shuban

  • Hero Member
  • *****
  • Posts: 637
    • View Profile
    • Biology Forums - For All Your Science Needs
Re: SQL Query Slow (Suggestions)
« Reply #2 on: July 09, 2018, 12:22:38 pm »
The explain gives:

While the describe gives:



Offline SMFHacks

  • Administrator
  • Hero Member
  • *****
  • Posts: 14551
    • View Profile
Re: SQL Query Slow (Suggestions)
« Reply #3 on: July 09, 2018, 12:28:32 pm »
You need to add an index on postertime

ALTER table smf_messages add index posterTime (posterTime);
Get your Forum Ranked! at https://www.forumrankings.net - find out how your forum compares with others!

Like What I do? Support me at https://www.patreon.com/vbgamer45/

Offline shuban

  • Hero Member
  • *****
  • Posts: 637
    • View Profile
    • Biology Forums - For All Your Science Needs
Re: SQL Query Slow (Suggestions)
« Reply #4 on: July 09, 2018, 12:39:30 pm »
You need to add an index on postertime

ALTER table smf_messages add index posterTime (posterTime);

How did you determine that?

Offline SMFHacks

  • Administrator
  • Hero Member
  • *****
  • Posts: 14551
    • View Profile
Re: SQL Query Slow (Suggestions)
« Reply #5 on: July 09, 2018, 12:41:32 pm »
Was used in the where clause and I saw no index was on that column
Get your Forum Ranked! at https://www.forumrankings.net - find out how your forum compares with others!

Like What I do? Support me at https://www.patreon.com/vbgamer45/

Offline shuban

  • Hero Member
  • *****
  • Posts: 637
    • View Profile
    • Biology Forums - For All Your Science Needs
Re: SQL Query Slow (Suggestions)
« Reply #6 on: July 09, 2018, 01:02:18 pm »
It now loads in less than 1 second.

I noticed the KEY is now mul. I presume that's what was altered. How come SMF doesn't come stock that way?

Offline SMFHacks

  • Administrator
  • Hero Member
  • *****
  • Posts: 14551
    • View Profile
Re: SQL Query Slow (Suggestions)
« Reply #7 on: July 09, 2018, 01:08:59 pm »
Yes that was altered.
It might in new versions.
SMF only adds indexes to columns that are used in the where clause. Postertime might not have been used anywhere before.
Get your Forum Ranked! at https://www.forumrankings.net - find out how your forum compares with others!

Like What I do? Support me at https://www.patreon.com/vbgamer45/

Offline shuban

  • Hero Member
  • *****
  • Posts: 637
    • View Profile
    • Biology Forums - For All Your Science Needs
Re: SQL Query Slow (Suggestions)
« Reply #8 on: August 02, 2018, 08:21:02 pm »
I'm curious, does this need to be done every once in a while?

Offline SMFHacks

  • Administrator
  • Hero Member
  • *****
  • Posts: 14551
    • View Profile
Re: SQL Query Slow (Suggestions)
« Reply #9 on: August 02, 2018, 08:22:10 pm »
Index only has to be added once.
Get your Forum Ranked! at https://www.forumrankings.net - find out how your forum compares with others!

Like What I do? Support me at https://www.patreon.com/vbgamer45/

Offline shuban

  • Hero Member
  • *****
  • Posts: 637
    • View Profile
    • Biology Forums - For All Your Science Needs
Re: SQL Query Slow (Suggestions)
« Reply #10 on: August 02, 2018, 10:07:47 pm »
Index only has to be added once.

Reason why I asked is because suddenly it started going slow again :-\ Prior, it would load in less than a second. Nothing has changed, coding wise.
« Last Edit: August 02, 2018, 10:12:49 pm by shuban »

 

Related Topics

  Subject / Started by Replies Last post
4 Replies
4138 Views
Last post March 31, 2007, 01:37:52 pm
by SMFHacks
6 Replies
4890 Views
Last post April 12, 2011, 03:31:19 pm
by bruno
3 Replies
1926 Views
Last post December 09, 2014, 12:35:39 pm
by shuban
12 Replies
1822 Views
Last post February 10, 2017, 08:56:57 am
by GeorG
36 Replies
895 Views
Last post March 11, 2018, 11:03:55 pm
by TeraS

+- Recent Topics

link for users gallery from profile page by SMFHacks
August 20, 2018, 02:40:58 pm

[Mod]Tidy Child Boards 2.0 by SMFHacks
August 20, 2018, 01:32:43 pm

Admin View in Member's Profile of Dates for Accepting of PP and Reg Agreement by SMFHacks
August 18, 2018, 12:03:07 pm

Gallery2 Template Loading Error by SMFHacks
August 18, 2018, 12:02:12 pm

8: Undefined index: description by SMFHacks
August 17, 2018, 01:42:08 pm

It just does not send by SMFHacks
August 16, 2018, 11:03:06 pm

Ad beginning of post by SMFHacks
August 16, 2018, 10:22:58 pm

Agreement Date says NEVER by SMFHacks
August 14, 2018, 04:46:48 pm

HashTags system for SMF 2.0 released by Diego Andrés
August 12, 2018, 02:04:24 pm

Can the Store produce a unique code that you can sell? by SMFHacks
August 11, 2018, 11:43:32 am

Powered by EzPortal