I have a PhpBB board with about 3000 users and 500.000 posts. On the last days I notice that some topics uses long time to opened than the standard (about 2 seconds instead 0.05 seconds). So I made a debug to investigate and I see that it's a query fault. It's the same query that take different time to be executed, depending on the topic that it goes to request. For example this query take 1.7011 seconds to be executed:
SELECT u.username, u.user_id, u.user_active, u.user_mask, u.user_color, u.user_first_name, u.user_last_name, u.user_posts, u.user_from, u.user_long, u.user_lat, u.user_from_flag, u.user_website, u.user_email, u.user_aim, u.user_facebook, u.user_flickr, u.user_googleplus, u.user_icq, u.user_jabber, u.user_linkedin, u.user_msnm, u.user_skype, u.user_twitter, u.user_yim, u.user_youtube, u.user_regdate, u.user_allow_viewemail, u.user_rank, u.user_rank2, u.user_rank3, u.user_rank4, u.user_rank5, u.user_sig, u.user_avatar, u.user_avatar_type, u.user_allowavatar, u.user_allowsmile, u.user_allow_viewonline, u.user_session_time, u.user_warnings, u.user_level, u.user_birthday, u.user_next_birthday_greeting, u.user_gender, u.user_personal_pics_count, u.user_style, u.user_lang, u.preferenza_meteo, u.stazione_meteo, u.ct_miserable_user, p.*, t.topic_poster, t.title_compl_infos
FROM phpbb_posts p, phpbb_users u, phpbb_topics t
WHERE p.topic_id = 17278
AND t.topic_id = p.topic_id
AND u.user_id = p.poster_id
ORDER BY p.post_time ASC
LIMIT 0, 15
This query, that it's the same, but it using a different ID topic in WHERE clause, take 0.0015 seconds, that was the normal time for this kind of DB request.
SELECT u.username, u.user_id, u.user_active, u.user_mask, u.user_color, u.user_first_name, u.user_last_name, u.user_posts, u.user_from, u.user_long, u.user_lat, u.user_from_flag, u.user_website, u.user_email, u.user_aim, u.user_facebook, u.user_flickr, u.user_googleplus, u.user_icq, u.user_jabber, u.user_linkedin, u.user_msnm, u.user_skype, u.user_twitter, u.user_yim, u.user_youtube, u.user_regdate, u.user_allow_viewemail, u.user_rank, u.user_rank2, u.user_rank3, u.user_rank4, u.user_rank5, u.user_sig, u.user_avatar, u.user_avatar_type, u.user_allowavatar, u.user_allowsmile, u.user_allow_viewonline, u.user_session_time, u.user_warnings, u.user_level, u.user_birthday, u.user_next_birthday_greeting, u.user_gender, u.user_personal_pics_count, u.user_style, u.user_lang, u.preferenza_meteo, u.stazione_meteo, u.ct_miserable_user, p.*, t.topic_poster, t.title_compl_infos
FROM phpbb_posts p, phpbb_users u, phpbb_topics t
WHERE p.topic_id = 17250
AND t.topic_id = p.topic_id
AND u.user_id = p.poster_id
ORDER BY p.post_time ASC
LIMIT 0, 15
This is the EXPLAIN result for this query:
I use this board since about 15 years, and I never had notice similar problem, and similar timings to open a topic. But I'm not able to understand where the problem could be.
I solved (I hope) converting the phpbb_posts table in INNODB instead MyISAM. I don't know if this is the best solution, but now all query run quicklyand using the right KEY INDEX.
The first query I posted on first post, that before previously took 1.7011 seconds to be executed, now require only 0.0017 seconds.