Minimize dependence on SQL_CALC_FOUND_ROWS

I’m just looking for opinions / knowledge. I am currently handling a large WooCommerce store and frequently come across SQL queries needing optimization.

Some of the queries use indexes well but the pagination bit introduces a slight delay (usually around 0.5s – 0.9s) due to SQL_CALC_FOUND_ROWS. MySQL ends up using a temporary table + file sort according to EXPLAIN on the queries only when I add SQL_CALC_FOUND_ROWS.

Is there a way to optimize for SQL_CALC_FOUND_ROWS?

Do you think it would ever be possible for WordPress / WooCommerce to work in an environment where they don’t know the total number of posts (i.e. last page) in the backend?

Some of my not-well-thought ideas include caching the total pages for some period of time on the first execution and then the subsequent pagination queries not adding SQL_CALC_FOUND_ROWS to the query. ??

• This topic was modified 3 years, 8 months ago by Pratham.
• This topic was modified 3 years, 8 months ago by Jan Dembowski. Reason: Moved to Fixing WordPress, this is not an Everything else WordPress topic