Also solved the slow topics conversion issue:
Replaced
SELECT
t.tid AS ID_TOPIC, t.pinned AS isSticky, t.forum_id AS ID_BOARD,
t.starter_id AS ID_MEMBER_STARTED, t.last_poster_id AS ID_MEMBER_UPDATED
,
pl.pid AS ID_POLL, t.posts AS numReplies, t.views AS numViews,
MIN(p.pid) AS ID_FIRST_MSG, MAX(p.pid) AS ID_LAST_MSG,
t.state = 'closed' AS locked
FROM ({$from_prefix}topics AS t, {$from_prefix}posts AS p)
LEFT JOIN {$from_prefix}polls AS pl ON (pl.tid = t.tid)
WHERE p.topic_id = t.tid
GROUP BY t.tid
HAVING ID_FIRST_MSG != 0
AND ID_LAST_MSG != 0;
with
SELECT
ID_TOPIC, isSticky, ID_BOARD, ID_MEMBER_STARTED, ID_MEMBER_UPDATED,
pl.pid AS ID_POLL, numReplies, numViews, ID_FIRST_MSG, ID_LAST_MSG,
locked
FROM ( SELECT
t.tid AS ID_TOPIC, t.pinned AS isSticky,
t.forum_id AS ID_BOARD, t.starter_id AS ID_MEMBER_STARTED,
t.last_poster_id AS ID_MEMBER_UPDATED, t.posts AS numReplies,
t.views AS numViews, MIN(p.pid) AS ID_FIRST_MSG,
MAX(p.pid) AS ID_LAST_MSG, t.state = 'closed' AS locked
FROM ({$from_prefix}topics AS t, {$from_prefix}posts AS p)
WHERE p.topic_id = t.tid
GROUP BY t.tid
HAVING ID_FIRST_MSG != 0 AND ID_LAST_MSG != 0) res
LEFT JOIN {$from_prefix}polls AS pl ON (pl.tid = res.ID_TOPIC);
Idea from hxxp:www.mysqlperformanceblog.com/2007/04/06/using-delayed-join-to-optimize-count-and-limit-queries [nonactive].
Makes a LOT of sense in this case, to delay the JOIN till after we get the trimmed list, rather than do a BIG JOIN, then try to filter that.
VERY fast now. 26107 rows in less than 2 mins.
Partitioned in 10000 to avoid the max packet limit.
