Long query in MySQL/InnoDB - How to speed up WooCommerce?

Question

MySQL

Good afternoon!
There is an online store > 3 million products.
The search for goods, and in general the catalog itself, works for a very long time.
Improving the server (RAM) and optimizing my.cnf did not give any result, or rather, setting my.cnf gave the result (the server stopped giving 504,502 errors, and requests are cached in general, that is, what was recently requested - was searched for quickly, and what is new - no)
All pages fly, WooCommerce itself slows down - it takes a very long time to load.
It slows down all MySQL, these are the queries, from 14-17 seconds

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1  AND ( 
  ( wp_postmeta.meta_key = '_visibility' AND wp_postmeta.meta_value IN ('visible','catalog') )
) AND wp_posts.post_type = 'product' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_posts.menu_order ASC, wp_posts.post_title ASC LIMIT 0, 10;

How can it be optimized?
Thank you!

Answer 1

in this particular case, it seems to me, "SQL_CALC_FOUND_ROWS" spoils everything for you. Because of it, all ~2M lines are read. You definitely need to know. how many rows are in your sample?
either edit the code or add memory so that the cache is not cleared often. Or upgrade to an SSD. Although with such tables ...