Getting slow query from SELECT COUNT(*) on WordPress-CodePudding

I have the query below and it gives me a count of 32,000.

SELECT  COUNT(*)
    FROM  wp_term_relationships, wp_posts
    WHERE  wp_posts.ID = wp_term_relationships.object_id
      AND  post_status IN ('publish')
      AND  post_type IN ('ac_listings', 'ac_fields')

# Query_time: 3.324099  Lock_time: 0.000061  Rows_sent: 1  Rows_examined: 107818
# Rows_affected: 0  Bytes_sent: 67

But it appears quite often on my MariaDB slow query log. How could I fix this?

These are the indices which are the default ones from WP.

PRIMARY KEY (`ID`),
INDEX `post_name` (`post_name`(191)),
INDEX `type_status_date` (`post_type`, `post_status`, `post_date`, `ID`),
INDEX `post_parent` (`post_parent`),
INDEX `post_author` (`post_author`)


PRIMARY KEY (`object_id`, `term_taxonomy_id`),
INDEX `term_taxonomy_id` (`term_taxonomy_id`)

P/S: I'm guessing this might be causing my site's intermittent db connection error. I'm running a micro t2 and 4GB RAM on AWS EC2 and I only have 8 people/Editors on the site simultaneously.

CodePudding user response：

posts needs a composite index starting with these two columns, in this order:

INDEX(post_status, post_type)

With that, I expect to see the Query time diminish and also Rows_examined: 107818. Still it will take some time to wade through all the 32K relations for some number of posts.

See also: WP Index Improvements , especially for queries using post_meta.