[3.0] Slow moderation query

[jdarwood007]

This is against 2.1, but I suspect the same problem exists for 3.0 due to the similar database.

SMF/Sources/PostModeration.php

Lines 189 to 200 in d6dbdbd

	$request = $smcFunc['db_query']('', '
	SELECT COUNT(*)
	FROM {db_prefix}messages AS m
	INNER JOIN {db_prefix}topics AS t ON (t.id_topic = m.id_topic AND t.id_first_msg != m.id_msg)
	INNER JOIN {db_prefix}boards AS b ON (b.id_board = t.id_board)
	WHERE m.approved = {int:not_approved}
	AND {query_see_board}
	' . $approve_query,
	array(
	'not_approved' => 0,
	)
	);

SMF/Sources/PostModeration.php

Lines 243 to 262 in d6dbdbd

	$request = $smcFunc['db_query']('', '
	SELECT m.id_msg, m.id_topic, m.id_board, m.subject, m.body, m.id_member,
	COALESCE(mem.real_name, m.poster_name) AS poster_name, m.poster_time, m.smileys_enabled,
	t.id_member_started, t.id_first_msg, b.name AS board_name, c.id_cat, c.name AS cat_name
	FROM {db_prefix}messages AS m
	INNER JOIN {db_prefix}topics AS t ON (t.id_topic = m.id_topic)
	INNER JOIN {db_prefix}boards AS b ON (b.id_board = m.id_board)
	LEFT JOIN {db_prefix}members AS mem ON (mem.id_member = m.id_member)
	LEFT JOIN {db_prefix}categories AS c ON (c.id_cat = b.id_cat)
	WHERE m.approved = {int:not_approved}
	AND t.id_first_msg ' . ($context['current_view'] == 'topics' ? '=' : '!=') . ' m.id_msg
	AND {query_see_board}
	' . $approve_query . '
	LIMIT {int:start}, {int:limit}',
	array(
	'not_approved' => 0,
	'start' => $context['start'],
	'limit' => $limit,
	)
	);

It appears that the approved index is not being hit. Adding an index for that improves this query significantly on a large forum (6 seconds for both to < 200 ms)

ALTER TABLE smf_messages 
ADD INDEX idx_approved_topic_msg (approved, id_topic, id_msg);