How can I optimize this wordpress mysql query?

Question

I have a mysql query which is really bogging down my VPS. Its doing a filesort according to explain extended. I usually optimize queries on my own but this one i'm not sure I know how.

SELECT DISTINCT SQL_CALC_FOUND_ROWS wp_posts.*,MAX((1.00 * (MATCH(wp_search_post.post_content) AGAINST ('test' IN BOOLEAN MODE))) + (2.00 * (MATCH(wp_search_post.post_title) AGAINST ('test' IN BOOLEAN MODE))) + (1.00 * (MATCH(wp_search_post.post_excerpt) AGAINST ('test' IN BOOLEAN MODE))) + (1.00 * (MATCH(wp_search_post.post_meta) AGAINST ('test' IN BOOLEAN MODE))) + (2.00 * (MATCH(wp_search_post.post_tags) AGAINST ('test' IN BOOLEAN MODE)))) AS score FROM wp_posts LEFT JOIN wp_search_post ON wp_posts.ID=wp_search_post.post_id  WHERE 1=1 AND (wp_posts.post_status='publish' OR wp_posts.post_password='')  AND ((MATCH(wp_search_post.post_content,wp_search_post.post_title,wp_search_post.post_excerpt,wp_search_post.post_meta,wp_search_post.post_tags) AGAINST ('test' IN BOOLEAN MODE)))  GROUP BY wp_posts.ID HAVING score ORDER BY score DESC,wp_posts.post_date DESC LIMIT 60, 20;

Any help is greatly appreciated.

Answer 1

As far as I remember there is a known issue of SQL_CALC_FOUND_ROWS with the tables that have full-text indexing.

We had a similar issue in our application, we solved the issue by removing the SQL_CALC_FOUND_ROWS from the query and issued a separate count query to get the count of results found when required.

You can also try this out, just run the query w/o SQL_CALC_FOUND_ROWS and see if it makes a difference.

Check out this link for details.