My b2evolution Version: Not Entered
I've got a 1.9.3 install that's producing some slow queries, from 12 to 23 seconds. I'm capturing the queries in my slow query log:
SELECT DISTINCT evo_comments.* FROM evo_comments INNER JOIN evo_posts ON comment_post_ID = post_ID INNER JOIN evo_postcats ON post_ID = postcat_post_ID INNER JOIN evo_categories othercats ON postcat_cat_ID = othercats.cat_ID WHERE othercats.cat_blog_ID = 2 AND comment_type IN ('comment','trackback','pingback') AND comment_status IN ('published') AND ( ( post_status = "private" AND post_creator_user_ID = 1 ) OR post_status IN ('published','protected') ) ORDER BY comment_date DESC LIMIT 20;
I've tried to speed things up a bit by creating a couple of indexes in evo_comments, now my explain looks like this (sorry for the formatting):
+----+-------------+--------------+--------+------------------------------------------+--------------+---------+-----------------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+--------+------------------------------------------+--------------+---------+-----------------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | evo_comments | range | comment_post_ID,comment_type,comment_idx | comment_type | 1 | NULL | 18532 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | evo_posts | eq_ref | PRIMARY,post_status,post_creator_user_ID | PRIMARY | 4 | b2mm.evo_comments.comment_post_ID | 1 | Using where; Distinct |
| 1 | SIMPLE | evo_postcats | ref | PRIMARY,catpost | PRIMARY | 4 | b2mm.evo_posts.post_ID | 2 | Using where; Using index; Distinct |
| 1 | SIMPLE | othercats | eq_ref | PRIMARY,cat_blog_ID | PRIMARY | 4 | b2mm.evo_postcats.postcat_cat_ID | 1 | Using where; Distinct |
+----+-------------+--------------+--------+------------------------------------------+--------------+---------+-----------------------------------+-------+----------------------------------------------+
4 rows in set (0.00 sec)
Does anyone have any suggestions in terms of indexes or optimization to get these query times down?
I'm not sure if this is related but I have to assume the MYSQL server is overwhelmed. When my server is a little bit busy I get mysql errors in the sidebar. It starts in the sidebar directly where the Admin link is in the skin. What is this about? :