0

I'm new to MySQL. I'm getting this slow query below.

# Query_time: 3.357236  Lock_time: 0.000054  Rows_sent: 1  Rows_examined: 107494
# Rows_affected: 0  Bytes_sent: 67
SET timestamp=1652612113;
SELECT COUNT(*) FROM et_term_relationships, et_posts WHERE et_posts.ID = et_term_relationships.object_id AND post_status IN ('publish') AND post_type IN ('acadp_listings') AND term_taxonomy_id = 15;

What index (with what columns) should I create and what type of index should I use (PRIMARY KEY, UNIQUE, INDEX, FULLTEXT)?

Any help is appreciated.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
mewiben39
  • 121
  • 9
  • You might try this plugin to update your indexes. https://wordpress.org/plugins/index-wp-mysql-for-speed/ When I tried it I got a slightly better execution plan. But, it must be said: COUNT(*) can't be made astonishingly fast when dealing with large tables. – O. Jones May 17 '22 at 12:59
  • @O.Jones thanks but I'm already using it and I'm still getting slow queries sometimes, caused by other plugins and custom post types. – mewiben39 May 17 '22 at 13:09
  • If you put in a support ticket at https://wordpress.org/support/plugin/index-wp-mysql-for-speed/ I'll take a look. The indexes are already close to ideal for a query like yours. – O. Jones May 17 '22 at 13:12
  • @O.Jones I wish I could but Wordpress.org indiscriminately banned me from their site so I can't post the tick. It's a long story, but I know some developers got banned for no legit reasons too. – mewiben39 May 17 '22 at 13:28
  • How about on https://github.com/OllieJones/index-wp-mysql-for-speed/issues ? – O. Jones May 17 '22 at 13:38
  • Please convert the tables to ENGINE=InnoDB and adjust key_buffer_size (down) and innod_buffer_pool_size (up). – Rick James May 19 '22 at 02:27

0 Answers0