Boards load extremely slow! How to improve

[Biology Forums]

Whenever I want to load a board to view the topics inside, it takes forever to load sometimes.

Here's the code that takes a long time:

Code Select Expand

SELECT
t.id_topic, t.num_replies, t.locked, t.num_views, t.is_sticky, t.id_poll, t.id_previous_board,
IFNULL(lt.id_msg, IFNULL(lmr.id_msg, -1)) + 1 AS new_from,
t.id_last_msg, t.approved, t.unapproved_posts, ml.poster_time AS last_poster_time,
ml.id_msg_modified, ml.subject AS last_subject, ml.icon AS last_icon,
ml.poster_name AS last_member_name, ml.id_member AS last_id_member,
IFNULL(meml.real_name, ml.poster_name) AS last_display_name, t.id_first_msg,
mf.poster_time AS first_poster_time, mf.subject AS first_subject, mf.icon AS first_icon,
mf.poster_name AS first_member_name, mf.id_member AS first_id_member,
IFNULL(memf.real_name, mf.poster_name) AS first_display_name, SUBSTRING(ml.body, 1, 385) AS last_body,
SUBSTRING(mf.body, 1, 385) AS first_body, ml.smileys_enabled AS last_smileys, mf.smileys_enabled AS first_smileys
,`ba`.`id_msg` as `best_answer`
FROM smf_topics AS t
INNER JOIN smf_messages AS ml ON (ml.id_msg = t.id_last_msg)
INNER JOIN smf_messages AS mf ON (mf.id_msg = t.id_first_msg)
LEFT JOIN smf_members AS meml ON (meml.id_member = ml.id_member)
LEFT JOIN smf_members AS memf ON (memf.id_member = mf.id_member)
LEFT JOIN smf_log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = 1)
LEFT JOIN smf_log_mark_read AS lmr ON (lmr.id_board = 33 AND lmr.id_member = 1)
LEFT JOIN smf_best_answer AS `ba` ON `t`.`id_topic` = `ba`.`id_topic`
WHERE t.id_board = 33
ORDER BY is_sticky DESC, id_last_msg DESC
LIMIT 0, 25

So far I've waited 567 seconds.

Anyone see the issue?

[vbgamer45]

I want to see how best answer table is made in sql

Run this query
show create table smf_best_answer;

[Biology Forums]

Attached.

[Arantor]

Can we also get an explain of the original query to see what MySQL did to it?

[Biology Forums]

I'll cut this short.

The issue was with smf_best_answer. I removed from the sql command it since it wasn't absolutely vital for the forum and now it's lightning fast.

Now the question, how do I optimize the query with smf_best_answer?

[vbgamer45]

Can you post the full table  smf_best_answer
You probably are missing indexes on id_topic in the table.

[Biology Forums]

Thanks for the help, here it is

[vbgamer45]

run

Code Select Expand


alter table smf_best_answer add index id_topic (id_topic);


[Biology Forums]

That did it

What does that do?

[vbgamer45]

Adds an index. When you do a SQL where clause most of time you want to put an index on the column being referenced.

[Biology Forums]

ty