Why does this SQL query take so long?

Started by Biology Forums, July 08, 2018, 11:36:10 PM

Previous topic - Next topic

Biology Forums

Here it is in action:

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.

albertlast

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.

GigaWatt

"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?"..."

Biology Forums

Quote from: albertlast 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.

Please elaborate

Kindred

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...
Слaва
Украинi

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

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

Biology Forums

*** Still waiting for someone to actually help me understand why the QUERY is slow apart from SMF 1 being outdated.


Kindred

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
Слaва
Украинi

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

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

Arantor

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.

vbgamer45

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

Arantor

I just tire of having to say it multiple times to the same person.

Kindred

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
Слaва
Украинi

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

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

Advertisement: