Might add that each product can have upto 14 attributes in which they can be filtered by in each product category. For instance, one category has over 30k products with 13 filterable attributes.
Your monitor has two nasty queries in it. Both ran just once in the minute for which you captured the monitor, and each one took over two seconds (slow!). Both ran on the front-end (visible to your users). They look like they are product-listing pages.
The queries display the first 15 products, and support the display of a pagination widget something like this:
Showing 1-15 of 650 products 1 2 3 4 … 40 41 42
Figuring out the total number of products to render is the expensive part of these queries. If it were possible to replace that pagination widget with
Showing 15 products. Show more>
It would cut down the query time. Maybe there’s an opportunity for another plugin. (And maybe one already exists. If I find one I’ll mention it here.)
And your elaborate filtering arrangement also costs some performance.
Yes, the nasty query you speak of is a product category page as I mentioned above. The query displays 50 products per page (not sure why it says 15) with total of over 30k products.
Are you suggesting working on the pagination “show more” to help cut down query time? Currently using the following for paging.
$loop->found_posts;
$loop->max_num_pages;
As for the filtering, that is mandatory for this business.
(Of course you need product filtering. I didn’t mean to imply you don’t. It still costs performance.)
The pagination part of WP_Query causes queries to contain the performance-leeching SQL_CALC_FOUND_ROWS option. The effect of that option is to run the query to completion even after it delivers its LIMIT of rows, so the DBMS can then deliver the total number of rows the query would have delivered without a LIMIT clause. That total number of rows then gets used to compute the number of pages of results that can be shown. In turn that is used to populate the pagination widget on the page.
A simpler pagination widget layout that did not need to know the total number of pages available (or the page-number of the last page, which is the same thing) would not need SQL_CALC_FOUND_ROWS and therefore would be faster.
I did some deep-dive mucking around with query efficency for the Index WP Users for Speed plugin. I believe, but I’m not totally sure, that the hooks exist to display a simpler pagination widget and avoid SQL_CALC_FOUND_ROWS. I know it will help performance in many stores with large product counts.
That’s what I have in mind.
The plugin review queue is now 88 days long !!! Christmas! If I do develop this, I guess I’ll have to publish early versions myself and not in this plugin repository.
Makes perfect sense, Ollie. Didn’t even take that into consideration. Will look into this as well.
Thank you.
Using a persistent object cache helps reduce the number of expensive SQL_CALC_FOUND_ROWS queries: it caches the results of those queries and re-uses them.
I have done some work on this SQL_CALC_FOUND_ROWS performance antipattern in WordPress Core. Here is a first draft of a plugin to cache those results.
https://www.plumislandmedia.net/wordpress/plugin/sql_calc_found_rows_slow_wordpress/
This requires a persistent object cache to do anything useful.
Ollie,
Thank you for working on this.. I have installed the first draft and do see improvement. I am going to monitor over the next few days and I’ll report back to you.
