[Enhancement]: Improve slow admin orders list page

[gedex]

Describe the solution you'd like

Opening admin order list page can be pretty slow due to some queries. Some notable slow queries:

Opening admin posts (wp_posts total rows are bigger than wp_wc_orders) list is not that slow and total time of DB queries is less than a second.

Based on these slow queries, there are small quick wins that would improve the total queries time.

Cache count queries

From the list of slow queries, there are two count queries:

Count of all orders

SELECT COUNT(DISTINCT wp_wc_orders.id)
FROM wp_wc_orders
WHERE 1=1
AND (wp_wc_orders.status IN ('wc-pending','wc-processing','wc-on-hold','wc-completed','wc-cancelled','wc-refunded','wc-failed'))
AND (wp_wc_orders.type = 'shop_order')

Count of orders grouped by status

SELECT status, COUNT(*) AS cnt
FROM wp_wc_orders
WHERE type = 'shop_order'
GROUP BY status

The results then used by list table views and both queries are not cached.

The WP_Posts_List_Table uses wp_count_posts() for the similar purpose, but it caches the result. WC has OrderUtiil::get_count_for_type() that counts similarly.

Cache and improve month dropdown query

SELECT DISTINCT YEAR( t.date_created_local ) AS year, MONTH( t.date_created_local ) AS month
FROM (
SELECT DATE_ADD( date_created_gmt, INTERVAL 7200 SECOND ) AS date_created_local
FROM wp_wc_orders
WHERE status != 'trash' ) t
ORDER BY year DESC, month DESC

While it's possible to short-circuit before performing the query, it's something a store without customizations would run and the result is always the same for at least a month. The result seems like a good candidate to cache with a long TTL.

Also, the query attempts to get the result in local timezone using subquery—thus making it slower than WP's dropdown query. What if the query just returns the date_created_gmt as is (or do the conversion in PHP)?

Describe alternatives you've considered

No response

Additional context

HPOS with sync disabled