Slow ssi_recentTopics due to boards join

[TracerX]

Hi guys, I have noticed that I am having responsiveness issues when I use ssi_recentTopics on a page. The current query (I removed some board exclusions I was using because it didn't appear to significantly affect query speed) averages about 4.04 seconds over 5 runs though that number gets higher at times (looks like it could be due to locking?):

Code Select Expand


SELECT
m.poster_time, ms.subject, m.id_topic, m.id_member, m.id_msg, b.id_board, b.name AS board_name, t.num_replies, t.num_views,
IFNULL(mem.real_name, m.poster_name) AS poster_name, 1 AS is_read, 0 AS new_from, SUBSTRING(m.body, 1, 384) AS body, m.smileys_enabled, m.icon
FROM core_topics AS t
INNER JOIN core_messages AS m ON (m.id_msg = t.id_last_msg)
INNER JOIN core_boards AS b ON (b.id_board = t.id_board)
INNER JOIN core_messages AS ms ON (ms.id_msg = t.id_first_msg)
LEFT JOIN core_members AS mem ON (mem.id_member = m.id_member)
WHERE t.id_last_msg >= 2402667
ORDER BY t.id_last_msg DESC
LIMIT 20


If I remove the inner join to core_boards and remove the two columns that require that board from the select, the execution time drops down to 0.384 over 5 runs.

I'm thinking that an index might help with this issue though I'm unsure of exactly what I should be looking to index. Any insight or suggestions would be greatly appreciated. 

EDIT: I should probably add that I'm on 2.0.4.

[Arantor]

I removed some board exclusions I was using because it didn't appear to significantly affect query speed

The board exclusions, presuming you mean {query_see_boards} are there not for performance but privacy, ensuring that only the boards users should be able to see can see it...

As far as pure performance goes, best way to analyse this is to take the query and put an EXPLAIN in front of it.

[TracerX]

Sorry for the confusion there, I meant that I had removed the exclusions from the query I was showing in order to limit the size of the query so it would be simpler to view. here is the result of the EXPLAIN for the query:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	b	ALL	PRIMARY				90	Using temporary; Using filesort
1	SIMPLE	t	ref	IX_ForumID	IX_ForumID	2	hxxp:smfdb.b.id [nonactive]_board	2333	Using where
1	SIMPLE	m	eq_ref	PRIMARY	PRIMARY	4	hxxp:smfdb.t.id [nonactive]_last_msg	1
1	SIMPLE	mem	eq_ref	PRIMARY	PRIMARY	3	hxxp:smfdb.m.id [nonactive]_member	1
1	SIMPLE	ms	eq_ref	PRIMARY	PRIMARY	4	hxxp:smfdb.t.id [nonactive]_first_msg	1

[Arantor]

I meant that I had removed the exclusions from the query I was showing in order to limit the size of the query so it would be simpler to view

The exclusion on the boards table is purely for validating board access. If you aren't using it to ensure access to boards is maintained (i.e. you don't care about anyone, even guests, seeing any post), there is no need to keep the table join.

Though given that query, I don't see why there is the filesort (which is the killer) on the boards table.

[TracerX]

The exclusion on the boards table is purely for validating board access. If you aren't using it to ensure access to boards is maintained (i.e. you don't care about anyone, even guests, seeing any post), there is no need to keep the table join.

Unfortunately, I do require the exclude boards since I have a miscellaneous board that is only accessed by certain users along with some boards that I don't want to show because they'd clutter up the recent topics. Here is my call to ssi_recentTopics:

Code Select Expand


$topics = ssi_recentTopics($num_recent = 20, $exclude_boards = array(80,26,93,94,95,96,97,98,99,100,101,102,103,105,106,107,108,109,120,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,151), $include_boards = null, $output_method = 'array');


and here is the full query for a guest user:

Code Select Expand


SELECT
m.poster_time, ms.subject, m.id_topic, m.id_member, m.id_msg, b.id_board, b.name AS board_name, t.num_replies, t.num_views,
IFNULL(mem.real_name, m.poster_name) AS poster_name, 1 AS is_read, 0 AS new_from, SUBSTRING(m.body, 1, 384) AS body, m.smileys_enabled, m.icon
FROM core_topics AS t
INNER JOIN core_messages AS m ON (m.id_msg = t.id_last_msg)
INNER JOIN core_boards AS b ON (b.id_board = t.id_board)
INNER JOIN core_messages AS ms ON (ms.id_msg = t.id_first_msg)
LEFT JOIN core_members AS mem ON (mem.id_member = m.id_member)
WHERE t.id_last_msg >= 2402667

AND b.id_board NOT IN (80, 26, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 105, 106, 107, 108, 109, 120, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 151)

AND (FIND_IN_SET(-1, b.member_groups) != 0)
ORDER BY t.id_last_msg DESC
LIMIT 20


[Arantor]

So is the EXPLAIN you gave for your original query or the actual query being used?

[TracerX]

It was on the modified query. Sorry, I should've realized that might make a difference. Here is explain run on the exact query as seen in my previous response. I also noted where NULL was being returned.

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	b	ALL	PRIMARY	NULL	NULL	NULL	90	Using where; Using temporary; Using filesort
1	SIMPLE	t	ref	IX_ForumID	IX_ForumID	2	hxxp:smfdb.b.id [nonactive]_board	805	Using where
1	SIMPLE	m	eq_ref	PRIMARY	PRIMARY	4	hxxp:smfdb.t.id [nonactive]_last_msg	1
1	SIMPLE	mem	eq_ref	PRIMARY	PRIMARY	3	hxxp:smfdb.m.id [nonactive]_member	1
1	SIMPLE	ms	eq_ref	PRIMARY	PRIMARY	4	hxxp:smfdb.t.id [nonactive]_first_msg	1

[Arantor]

I'm curious. Please DESCRIBE the structure of the boards table. There are no fields there that should be generating a filesort on that table.

In other news, can I make a tactical suggestion? How many boards is this drawing from (total boards less the exclude boards)? See, it may be faster to use include_boards rather than exclude_boards.