we can disable the online updates as we already converted the table asked about to InnoDB.
trying to understand the differences between 1.3.6 and 1.4.x social forum is still in the mix as its important to the users but we did try disabling from admincp
Here's an example of one of the slow queries. possible the xf_bb_code_parse_cache may be culprit. Checking.
# User@Host: vvvvv_user[vvvvv_user] @ localhost []
# Query_time: 6.073281 Lock_time: 0.000050 Rows_sent: 1 Rows_examined: 5
SET timestamp=1413410936;
SELECT post.*
,
bb_code_parse_cache.parse_tree AS message_parsed, bb_code_parse_cache.cache_version AS message_cache_version,
user.*, IF(user.username IS NULL, post.username, user.username) AS username,
user_profile.*,
signature_parse_cache.parse_tree AS signature_parsed, bb_code_parse_cache.cache_version AS signature_cache_version,
session_activity.view_date AS last_view_date,
0 AS like_date,
social_forum.logo_date,
social_forum.logo_width,
social_forum.logo_height,
social_forum.logo_crop_x,
social_forum.logo_crop_y,
social_forum.title AS social_forum_title,
social_forum_combination.cache_value AS secondary_social_forums
FROM xf_post AS post
LEFT JOIN xf_bb_code_parse_cache AS bb_code_parse_cache ON
(bb_code_parse_cache.content_type = 'post' AND bb_code_parse_cache.content_id = post.post_id)
LEFT JOIN xf_user AS user ON
(user.user_id = post.user_id)
LEFT JOIN xf_user_profile AS user_profile ON
(user_profile.user_id = post.user_id)
LEFT JOIN xf_bb_code_parse_cache AS signature_parse_cache ON
(signature_parse_cache.content_type = 'signature' AND signature_parse_cache.content_id = post.user_id)
LEFT JOIN xf_session_activity AS session_activity ON
(post.user_id > 0 AND session_activity.user_id = post.user_id)
LEFT JOIN xf_social_forum AS social_forum ON
(social_forum.social_forum_id = user_profile.primary_social_forum_id)
LEFT JOIN xf_social_forum_combination AS social_forum_combination ON
(social_forum_combination.social_forum_combination_id = user_profile.social_forum_combination_id)
WHERE post.thread_id = '941265'
AND (post.position >= 0 AND post.position < 30)
AND (post.message_state IN ('visible'))
ORDER BY post.position ASC, post.post_date ASC;
