Need help with slow MYSQL query & high CPU usage
Recently my MYSQL CPU usage went from 40% to over 100% and my server has been slowing down as a result. In doing some research I’ve learned to setup some mysql logging for slow queries. I have a particular query that happens often. In the query below it appears that over 700,000 rows are queried in the DB taking up almost 3 seconds. Imagine that times however many users and it is bogging the site down. Is this query below from bbpress or how can I tell? It’s hard to trace where it is being called:
# Time: 211220 17:42:06 # User@Host: mysite_wp[mysite_wp] @ localhost  # Thread_id: 1667 Schema: mysite_wp QC_hit: No # Query_time: 2.641487 Lock_time: 0.000104 Rows_sent: 10 Rows_examined: 717793 # Rows_affected: 0 Bytes_sent: 188 SET timestamp=1640047326; SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) WHERE 1=1 AND ( wp_postmeta.meta_key = '_bbp_last_active_time' AND ( ( mt1.meta_key = '_bbp_forum_id' AND CAST(mt1.meta_value AS SIGNED) != '1773' ) ) ) AND wp_posts.post_type = 'topic' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'closed') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10;
- You must be logged in to reply to this topic.