Advertisement:

Author Topic: Why does this SQL query take so long?  (Read 591 times)

Offline Study Force

  • SMF Hero
  • ******
  • Posts: 3,370
    • StudyForcePS on Facebook
    • @studyforceps on Twitter
    • Study Force
Why does this SQL query take so long?
« on: July 08, 2018, 11:36:10 PM »
Here it is in action:

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

What could be making it slow? Most of it was borrowed from SMF.

Offline albertlast

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 369
Re: Why does this SQL query take so long?
« Reply #1 on: July 09, 2018, 12:24:15 AM »
To keep it easy,
everything is wrong in this query.

I'm very sure that this query in this format didn't exists any more in smf2.1.

Online GigaWatt

  • The Smiley Guy
  • Support Specialist
  • SMF Hero
  • *
  • Posts: 1,447
  • Gender: Male
    • Macedonian electronics forum
Re: Why does this SQL query take so long?
« Reply #2 on: July 09, 2018, 06:47:51 AM »
He's on SMF 1.1.x.
"This is really a generic concept about human thinking - when faced with large tasks we're naturally inclined to try to break them down into a bunch of smaller tasks that together make up the whole."

"A 500 error loosely translates to the webserver saying, "WTF?"..."

Offline Study Force

  • SMF Hero
  • ******
  • Posts: 3,370
    • StudyForcePS on Facebook
    • @studyforceps on Twitter
    • Study Force
Re: Why does this SQL query take so long?
« Reply #3 on: July 09, 2018, 08:16:53 AM »
To keep it easy,
everything is wrong in this query.

I'm very sure that this query in this format didn't exists any more in smf2.1.

Please elaborate

Online Kindred

  • The Mean One
  • Support Specialist
  • SMF Legend
  • *
  • Posts: 57,345
  • Gender: Male
    • Kindred-999 on GitHub
Re: Why does this SQL query take so long?
« Reply #4 on: July 09, 2018, 09:03:53 AM »
bascially... you are on an unsupported version of SMF.
One of the reasons it is no longer supported is that the coding is outdated, inefficient and insecure.

You **REALLY** need to update to a modern version instead of patching the increasing number of holes in your hull with scotch tape...
Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

Offline Study Force

  • SMF Hero
  • ******
  • Posts: 3,370
    • StudyForcePS on Facebook
    • @studyforceps on Twitter
    • Study Force
Re: Why does this SQL query take so long?
« Reply #5 on: July 09, 2018, 10:39:11 AM »
*** Still waiting for someone to actually help me understand why the QUERY is slow apart from SMF 1 being outdated.


Online Kindred

  • The Mean One
  • Support Specialist
  • SMF Legend
  • *
  • Posts: 57,345
  • Gender: Male
    • Kindred-999 on GitHub
Re: Why does this SQL query take so long?
« Reply #6 on: July 09, 2018, 11:28:43 AM »
well....   the query is slow because LOOK AT IT!

you have conditionals in the SELECT statement
then you add a ton of joins
then more conditionals in the WHERE
basically, the system has to scan the entire topics table AND the messages table just to parse out the few that you want...  and don't forget the compares against the members and boards tables

AND the logs tables?

I am surprised that this query doesn't throw a conniption and break down in tears


Finally - you are asking for support on a product which as reached end of life....

Seriously... UPGRADE
Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 69,615
    • StoryBB/StoryBB on GitHub
Re: Why does this SQL query take so long?
« Reply #7 on: July 12, 2018, 08:15:08 AM »
And as ever, the only way to diagnose slow queries is to get the EXPLAIN on them, because many joins isn’t necessarily the only thing, it’s possible to have bad queries that don’t even have joins or many where clauses.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Online vbgamer45

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 20,643
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: Why does this SQL query take so long?
« Reply #8 on: July 12, 2018, 08:40:21 AM »
Yeah that's what I told him in https://www.smfhacks.com/index.php/topic,9612.0.html and how to fix.
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 69,615
    • StoryBB/StoryBB on GitHub
Re: Why does this SQL query take so long?
« Reply #9 on: July 12, 2018, 08:42:06 AM »
I just tire of having to say it multiple times to the same person.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Online Kindred

  • The Mean One
  • Support Specialist
  • SMF Legend
  • *
  • Posts: 57,345
  • Gender: Male
    • Kindred-999 on GitHub
Re: Why does this SQL query take so long?
« Reply #10 on: July 12, 2018, 08:45:29 AM »
of course he's still running a version that is outdated, basically unsupported and has known security issues which were not addressed in recent patches.

Don't know why he's putting so much effort into customizing that when it is already EoL and alot of what he's doing won't be transferable to 2.1
Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.