|
Forum > General SMF Forum
SQL Query Slow (Suggestions)
(1/3) > >>
shuban:
I posted this on the main SMF forum, but didn't get a favorable response. Was hoping to get SMFHack's expertise.
--- Code: ---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 --- End code ---
Sometimes takes a minute to load, what gives?
SMFHacks:
Can post the results of describe smf_messages;
And also run
--- Code: ---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;
--- End code ---
shuban:
The explain gives:
While the describe gives:
SMFHacks:
You need to add an index on postertime
ALTER table smf_messages add index posterTime (posterTime);
shuban:
--- Quote from: SMFHacks on July 09, 2018, 12:28:32 pm ---You need to add an index on postertime
ALTER table smf_messages add index posterTime (posterTime);
--- End quote ---
How did you determine that?
Navigation
[0] Message Index
[#] Next page |
Please Help!
by SMFHacks
April 17, 2024, 08:04:55 am
|
Rate own images
by fvlog19
April 11, 2024, 10:56:53 am
|
Tidy Child Boards on 2.1.4
by SMFHacks
April 04, 2024, 03:54:12 pm
|
Problems SMF 2.0.19 > 2.1.4 SMF Gallery Pro - Recents Images to overall header
by Michel68
March 30, 2024, 12:41:08 pm
|
Can't DROP 'id_member'; check that column/key exists Datei:
by SMFHacks
March 30, 2024, 11:58:20 am
|
No thumbnails on new uploads
by Tonyvic
March 29, 2024, 06:26:18 am
|
Display the Contact Page for guests
by SMFHacks
March 27, 2024, 10:55:43 am
|
is it possible to add support for odysee.com
by fvlog19
March 21, 2024, 08:47:51 am
|
Request for admin notification
by davejo
March 10, 2024, 01:31:59 am
|
I need help with torrent upload
by Ineedsmfhelp
March 09, 2024, 10:01:13 pm
|
|
|