Too many (5000+) database queries on shop page loads

[dynit]

Hello,

One of our sites is really getting slowed with too many Woocommerce database queries. I checked via Query Monitor and just browsing through categories (on the default shop page with the post type archive for products) fires 5000+ database queries per page load. The site has 66 categories, about 600 products, 2200 variations, and 7 custom attributes per product. Single product pages are 'only' 150-300 queries each, no performance issue there.

Running Woocommerce 3.5.5 , Wordpress 5.1, on a superfast host with PHP 7.3 and MariaDB 10.2.

Some performance issues are mitigated through caching (WP Rocket), but 5000+ queries is just too much. Changing themes to default didn't help, nor did disabling all plugins.

Top Woocommerce queries being fired on the default shop page with the post type archive for products are:

WP_Term_Query->get_terms() has 4.068 queries
update_meta_cache() has 631 queries
WP_Post::get_instance() has 379 queries
WC_Data_Store_WP->read_meta() has 366 queries
get_option() has 75 queries

It looks similar to these issues:

#15720
#19898

Any advice please? It looks like too many -irrelevant- details are loaded.

Thank you!

Screenshots

• This bug happens with a default WordPress theme active, or Storefront.
• I can reproduce this bug consistently using the steps above.

WordPress Environment

### WordPress Environment ###

WC Version: 3.5.5
Log Directory Writable: ✔
WP Version: 5.1
WP Multisite: –
WP Memory Limit: 1 GB
WP Debug Mode: –
WP Cron: ✔
Language: nl_NL
External object cache: –

Server Environment

Server Info: Apache/2
PHP Version: 7.3.2
PHP Post Max Size: 128 MB
PHP Time Limit: 60
PHP Max Input Vars: 2500
cURL Version: 7.29.0
NSS/3.19.1 Basic ECC

SUHOSIN Installed: –
MySQL Version: 5.5.5-10.2.13-MariaDB
Max Upload Size: 64 MB
Default Timezone is UTC: ✔
fsockopen/cURL: ✔
SoapClient: ✔
DOMDocument: ✔
GZip: ✔
Multibyte String: ✔
Remote Post: ✔
Remote Get: ✔

Database

WC Database Version: 3.5.5
WC Database Prefix: wpklun_
Totale databasegrootte: 1403.89MB
Database datagrootte: 716.93MB
Database index grootte: 686.96MB
wpklun_woocommerce_sessions: Data: 1.52MB + Index: 0.05MB
wpklun_woocommerce_api_keys: Data: 0.02MB + Index: 0.03MB
wpklun_woocommerce_attribute_taxonomies: Data: 0.02MB + Index: 0.02MB
wpklun_woocommerce_downloadable_product_permissions: Data: 28.56MB + Index: 54.84MB
wpklun_woocommerce_order_items: Data: 19.55MB + Index: 6.52MB
wpklun_woocommerce_order_itemmeta: Data: 141.69MB + Index: 127.31MB
wpklun_woocommerce_tax_rates: Data: 0.02MB + Index: 0.06MB
wpklun_woocommerce_tax_rate_locations: Data: 0.02MB + Index: 0.05MB
wpklun_woocommerce_shipping_zones: Data: 0.02MB + Index: 0.00MB
wpklun_woocommerce_shipping_zone_locations: Data: 0.02MB + Index: 0.05MB
wpklun_woocommerce_shipping_zone_methods: Data: 0.02MB + Index: 0.00MB
wpklun_woocommerce_payment_tokens: Data: 0.02MB + Index: 0.02MB
wpklun_woocommerce_payment_tokenmeta: Data: 0.02MB + Index: 0.03MB
wpklun_woocommerce_log: Data: 0.02MB + Index: 0.02MB
wpklun_commentmeta: Data: 1.52MB + Index: 3.03MB
wpklun_comments: Data: 36.56MB + Index: 28.09MB
wpklun_cptch_images: Data: 0.02MB + Index: 0.00MB
wpklun_cptch_packages: Data: 0.02MB + Index: 0.00MB
wpklun_cptch_whitelist: Data: 0.02MB + Index: 0.02MB
wpklun_duplicator_packages: Data: 0.02MB + Index: 0.02MB
wpklun_hctpc_images: Data: 0.02MB + Index: 0.00MB
wpklun_hctpc_packages: Data: 0.02MB + Index: 0.00MB
wpklun_hctpc_whitelist: Data: 0.02MB + Index: 0.02MB
wpklun_hidemysiteSecure: Data: 0.02MB + Index: 0.00MB
wpklun_links: Data: 0.02MB + Index: 0.02MB
wpklun_mollie_pending_payment: Data: 0.02MB + Index: 0.00MB
wpklun_options: Data: 2.34MB + Index: 0.22MB
wpklun_pdf_stamped_files_tbl: Data: 0.02MB + Index: 0.00MB
wpklun_postmeta: Data: 363.94MB + Index: 372.98MB
wpklun_posts: Data: 37.56MB + Index: 19.09MB
wpklun_smush_dir_images: Data: 0.02MB + Index: 0.03MB
wpklun_termmeta: Data: 0.06MB + Index: 0.06MB
wpklun_terms: Data: 0.06MB + Index: 0.03MB
wpklun_term_relationships: Data: 1.52MB + Index: 0.50MB
wpklun_term_taxonomy: Data: 0.06MB + Index: 0.06MB
wpklun_tinvwl_items: Data: 0.14MB + Index: 0.00MB
wpklun_tinvwl_lists: Data: 1.52MB + Index: 0.00MB
wpklun_usermeta: Data: 68.61MB + Index: 62.19MB
wpklun_users: Data: 4.52MB + Index: 5.55MB
wpklun_wcpdf_invoice_number: Data: 0.14MB + Index: 0.00MB
wpklun_wcpdf_proforma_number: Data: 0.06MB + Index: 0.00MB
wpklun_wc_download_log: Data: 6.52MB + Index: 6.03MB
wpklun_wc_webhooks: Data: 0.02MB + Index: 0.02MB
wpklun_wpmm_subscribers: Data: 0.02MB + Index: 0.00MB

Post Type Counts

attachment: 5266
export_template: 3
forum: 4
hrf_faq: 23
nav_menu_item: 33
oembed_cache: 1
page: 39
portfolio: 1
post: 140
product: 637
product_variation: 2200
revision: 6
scheduled_export: 2
shop_coupon: 11505
shop_order: 84338
shop_order_refund: 24
team_manager: 84
ticket: 438
tribe_wooticket: 1008
wpforms: 1
wpsc-product: 31
wpsc-product-file: 31

Settings

API Enabled: ✔
Force SSL: ✔
Currency: EUR (€)
Currency Position: left
Thousand Separator: .
Decimal Separator: ,
Number of Decimals: 2
Taxonomies: Product Types: external (external)
grouped (grouped)
simple (simple)
ticket-event (ticket-event)
variable (variable)

Taxonomies: Product Visibility: exclude-from-catalog (exclude-from-catalog)
exclude-from-catalog (exclude-from-catalog-2)
exclude-from-search (exclude-from-search)
exclude-from-search (exclude-from-search-2)
exclude-from-search (exclude-from-search-3)
featured (featured)
featured (featured-2)
outofstock (outofstock)
rated-1 (rated-1)
rated-2 (rated-2)
rated-3 (rated-3)
rated-3 (rated-3-2)
rated-4 (rated-4)
rated-4 (rated-4-2)
rated-5 (rated-5)
rated-5 (rated-5-2)

[Tofandel]

The performances improvement made by @mikejolley for WC 3.6 may tackle this issue
#22718

[dynit]

Thanks @Tofandel , I hope so, although it doesn't seem related to sorting. But then again, I'm not an expert on this. It just looks like it is loading all products and attributes for these page loads. Do you think https://github.com/woocommerce/woocommerce-product-tables-feature-plugin might solve the problem?

[MerzR]

Thanks @Tofandel , I hope so, although it doesn't seem related to sorting. But then again, I'm not an expert on this. It just looks like it is loading all products and attributes for these page loads. Do you think https://github.com/woocommerce/woocommerce-product-tables-feature-plugin might solve the problem?

I am running into some performance issues with variable products as well. The plugin you name is something a lot of people are waiting for a long time now. That wil surely solve a lot of performance issues with variable products!

[mikejolley]

This PR specifically should help here #22587

It will read the terms per set of variations rather than reading the terms of each individual variation.

[cosmoc0der]

I don't know how relevant this problem is, but I also had a problem with the long loading of the store page, the same get_terms() function was called 10,000 (!) Times.

The reason is getting the count of children items of a given category. For more details see this page https://docs.woocommerce.com/document/hide-sub-category-product-count-in-product-archives/ In short, to speed up loading, I added the following filter to the functions.php file

add_filter ('woocommerce_subcategory_count_html', '__return_false');

Now woocommerce shop page loading much faster