Products search on admin dashboard very slow!

Hi @vdgraphics,

As a first step, you’d want to do a conflict test to figure out if your theme or one of your plugins is causing slow queries. Follow our conflict testing guide here: https://docs.woocommerce.com/document/how-to-test-for-conflicts/.

For further troubleshooting, it’s recommended to install the Query Monitor plugin that has advanced features with complete debugging information.

Let us know what you find out.

I have Query monitor installed and it shows that slow query is coming from woocommerce plugin caller: WC_Product_Data_Store_CPT->search_products(), and this is the query:

SELECT DISTINCT posts.ID as product_id, posts.post_parent as parent_id
FROM wp0d_posts posts
LEFT JOIN wp0d_wc_product_meta_lookup wc_product_meta_lookup
ON posts.ID = wc_product_meta_lookup.product_id
LEFT JOIN wp0d_wc_product_meta_lookup parent_wc_product_meta_lookup
ON posts.post_type = 'product_variation'
AND parent_wc_product_meta_lookup.product_id = posts.post_parent
WHERE posts.post_type IN ('product','product_variation')
AND ( ( ( posts.post_title LIKE '%363670079954%')
OR ( posts.post_excerpt LIKE '%363670079954%')
OR ( posts.post_content LIKE '%363670079954%' )
OR ( wc_product_meta_lookup.sku LIKE '%363670079954%' )
OR ( wc_product_meta_lookup.sku = ''
AND parent_wc_product_meta_lookup.sku LIKE '%363670079954%' ) ))
ORDER BY posts.post_parent ASC, posts.post_title ASC

Hi @vdgraphics,

Thanks for sharing your Query Monitor output.

This query doesn’t help me much to figure out the root cause of the problem. Let’s see if there is an error on the WooCommerce Logs. Kindly provide us the following:

• System Status: You can find it via WooCommerce > Status. Select “Get system report” and then “Copy for support”. Once you’ve done that, paste it here in your response.
• Fatal Error Log: Share a copy of any fatal error log found under WooCommerce > Status > Logs (if available).

Thanks!

Hey Mirko,

Here is the system report from woocommerce:

### WordPress Environment ###

WordPress address (URL): https://irontime-sales.com
Site address (URL): https://irontime-sales.com
WC Version: 6.0.0
REST API Version: ? 6.0.0
WC Blocks Version: ? 6.3.3
Action Scheduler Version: ? 3.3.0
WC Admin Version: ? 2.9.4
Log Directory Writable: ?
WP Version: 5.8.3
WP Multisite: –
WP Memory Limit: 1 GB
WP Debug Mode: –
WP Cron: ?
Language: en_US
External object cache: –

### Server Environment ###

Server Info: Apache
PHP Version: 7.3.33
PHP Post Max Size: 256 MB
PHP Time Limit: 600
PHP Max Input Vars: 3000
cURL Version: 7.66.0
OpenSSL/1.1.1l-fips

SUHOSIN Installed: –
MySQL Version: 5.7.32-35-log
Max Upload Size: 256 MB
Default Timezone is UTC: ?
fsockopen/cURL: ?
SoapClient: ?
DOMDocument: ?
GZip: ?
Multibyte String: ?
Remote Post: ?
Remote Get: ?

### Database ###

WC Database Version: 6.0.0
WC Database Prefix: wp0d_
Total Database Size: 3816.68MB
Database Data Size: 3309.71MB
Database Index Size: 506.97MB
wp0d_woocommerce_sessions: Data: 63.11MB + Index: 4.11MB + Engine InnoDB
wp0d_woocommerce_api_keys: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp0d_woocommerce_attribute_taxonomies: Data: 0.09MB + Index: 0.05MB + Engine InnoDB
wp0d_woocommerce_downloadable_product_permissions: Data: 0.02MB + Index: 0.06MB + Engine InnoDB
wp0d_woocommerce_order_items: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp0d_woocommerce_order_itemmeta: Data: 2.52MB + Index: 2.75MB + Engine InnoDB
wp0d_woocommerce_tax_rates: Data: 0.02MB + Index: 0.06MB + Engine InnoDB
wp0d_woocommerce_tax_rate_locations: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp0d_woocommerce_shipping_zones: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp0d_woocommerce_shipping_zone_locations: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp0d_woocommerce_shipping_zone_methods: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp0d_woocommerce_payment_tokens: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp0d_woocommerce_payment_tokenmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp0d_woocommerce_log: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp0d_actionscheduler_actions: Data: 1.02MB + Index: 0.80MB + Engine InnoDB
wp0d_actionscheduler_claims: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp0d_actionscheduler_groups: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp0d_actionscheduler_logs: Data: 1.02MB + Index: 0.42MB + Engine InnoDB
wp0d_categorymeta: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp0d_commentmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp0d_comments: Data: 0.05MB + Index: 0.09MB + Engine InnoDB
wp0d_csp3_subscribers: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp0d_ebay_accounts: Data: 0.38MB + Index: 0.00MB + Engine InnoDB
wp0d_ebay_auctions: Data: 0.02MB + Index: 0.17MB + Engine InnoDB
wp0d_ebay_categories: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp0d_ebay_jobs: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp0d_ebay_log: Data: 0.02MB + Index: 0.05MB + Engine InnoDB
wp0d_ebay_messages: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp0d_ebay_orders: Data: 5.52MB + Index: 0.03MB + Engine InnoDB
wp0d_ebay_payment: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp0d_ebay_profiles: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp0d_ebay_shipping: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp0d_ebay_sites: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp0d_ebay_stocks_log: Data: 2.02MB + Index: 0.00MB + Engine InnoDB
wp0d_ebay_store_categories: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp0d_ebay_transactions: Data: 4.52MB + Index: 0.05MB + Engine InnoDB
wp0d_e_submissions: Data: 0.02MB + Index: 0.27MB + Engine InnoDB
wp0d_e_submissions_actions_log: Data: 0.02MB + Index: 0.11MB + Engine InnoDB
wp0d_e_submissions_values: Data: 0.05MB + Index: 0.03MB + Engine InnoDB
wp0d_links: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp0d_member_catmeta: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp0d_options: Data: 24.11MB + Index: 3.05MB + Engine InnoDB
wp0d_portfolio_catmeta: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp0d_postmeta: Data: 1221.98MB + Index: 253.52MB + Engine InnoDB
wp0d_posts: Data: 1443.95MB + Index: 64.27MB + Engine InnoDB
wp0d_product_catmeta: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp0d_termmeta: Data: 4.52MB + Index: 4.03MB + Engine InnoDB
wp0d_terms: Data: 4.52MB + Index: 5.03MB + Engine InnoDB
wp0d_term_relationships: Data: 16.55MB + Index: 8.52MB + Engine InnoDB
wp0d_term_taxonomy: Data: 4.52MB + Index: 4.03MB + Engine InnoDB
wp0d_usermeta: Data: 0.08MB + Index: 0.03MB + Engine InnoDB
wp0d_users: Data: 0.02MB + Index: 0.05MB + Engine InnoDB
wp0d_wc_admin_notes: Data: 0.05MB + Index: 0.00MB + Engine InnoDB
wp0d_wc_admin_note_actions: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp0d_wc_category_lookup: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp0d_wc_customer_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp0d_wc_download_log: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp0d_wc_order_coupon_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp0d_wc_order_product_lookup: Data: 0.02MB + Index: 0.06MB + Engine InnoDB
wp0d_wc_order_stats: Data: 0.02MB + Index: 0.05MB + Engine InnoDB
wp0d_wc_order_tax_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp0d_wc_product_meta_lookup: Data: 7.52MB + Index: 10.09MB + Engine InnoDB
wp0d_wc_rate_limits: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp0d_wc_reserved_stock: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp0d_wc_tax_rate_classes: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp0d_wc_webhooks: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp0d_wfblockediplog: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp0d_wfblocks7: Data: 0.02MB + Index: 0.05MB + Engine InnoDB
wp0d_wfconfig: Data: 2.52MB + Index: 0.00MB + Engine InnoDB
wp0d_wfcrawlers: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp0d_wffilechanges: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp0d_wffilemods: Data: 5.52MB + Index: 0.00MB + Engine InnoDB
wp0d_wfhits: Data: 1.02MB + Index: 0.05MB + Engine InnoDB
wp0d_wfhoover: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp0d_wfissues: Data: 0.02MB + Index: 0.06MB + Engine InnoDB
wp0d_wfknownfilelist: Data: 2.52MB + Index: 0.00MB + Engine InnoDB
wp0d_wflivetraffichuman: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp0d_wflocs: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp0d_wflogins: Data: 0.06MB + Index: 0.03MB + Engine InnoDB
wp0d_wfls_2fa_secrets: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp0d_wfls_settings: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp0d_wfnotifications: Data: 0.06MB + Index: 0.00MB + Engine InnoDB
wp0d_wfpendingissues: Data: 0.02MB + Index: 0.06MB + Engine InnoDB
wp0d_wfreversecache: Data: 0.11MB + Index: 0.00MB + Engine InnoDB
wp0d_wfsnipcache: Data: 0.02MB + Index: 0.05MB + Engine InnoDB
wp0d_wfstatus: Data: 0.13MB + Index: 0.11MB + Engine InnoDB
wp0d_wftrafficrates: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp0d_wpforms_tasks_meta: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp0d_wpmailsmtp_debug_events: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp0d_wpmailsmtp_emails_log: Data: 1.52MB + Index: 0.09MB + Engine InnoDB
wp0d_wpmailsmtp_tasks_meta: Data: 0.05MB + Index: 0.00MB + Engine InnoDB
wp0d_wpml_mails: Data: 0.13MB + Index: 0.00MB + Engine InnoDB
wp0d_wpts_activity_log: Data: 2.52MB + Index: 0.00MB + Engine InnoDB
wp0d_wt_iew_action_history: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp0d_wt_iew_cron: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp0d_wt_iew_ftp: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp0d_wt_iew_mapping_template: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp0d_yith_wcwl: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp0d_yith_wcwl_lists: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp0d_yoast_indexable: Data: 373.94MB + Index: 85.30MB + Engine InnoDB
wp0d_yoast_indexable_hierarchy: Data: 13.52MB + Index: 14.55MB + Engine InnoDB
wp0d_yoast_migrations: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp0d_yoast_primary_term: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp0d_yoast_seo_links: Data: 96.63MB + Index: 44.13MB + Engine InnoDB

### Post Type Counts ###

addify_quote: 22
addify_rfq: 2
addify_rfq_fields: 5
attachment: 285777
custom_css: 2
elementor_library: 8
elementor_snippet: 1
nav_menu_item: 14
oembed_cache: 4
page: 15
porto_builder: 13
post: 2
product: 57949
product_variation: 9
revision: 69
seedprod: 1
shop_coupon: 1
shop_order: 17
shop_order_refund: 1
wpcf7_contact_form: 2
wpforms: 1
yith_wcan_preset: 1

### Security ###

Secure connection (HTTPS): ?
Hide errors from visitors: ?

### Active Plugins (27) ###

Query Monitor: by John Blackbourn – 3.8.2
Auto Image Attributes From Filename With Bulk Updater: by Arun Basil Lal – 2.1
Classic Editor: by WordPress Contributors – 1.6.2
Coming Soon Page, Maintenance Mode & Landing Pages by SeedProd: by SeedProd – 6.6.0
Doofinder for WooCommerce: by doofinder – 1.5.17
Elementor Pro: by Elementor.com – 3.5.2
Elementor: by Elementor.com – 3.5.3
Enable jQuery Migrate Helper: by The WordPress Team – 1.3.0
Nav Menu Roles: by Kathy Darling – 2.0.1
Porto Theme - Functionality: by P-Themes – 2.2.1
Product CSV Import Export (BASIC): by WebToffee – 2.1.3
reCaptcha for WooCommerce: by I Thirteen Web Solution – 2.16
SiteGround Optimizer: by SiteGround – 6.0.5
Tawk.to Live Chat: by Tawkto – 0.6.0
WooCommerce PayTrace Payment Gateway: by VanboDevelops – 2.5.9
Import from eBay to WooCommerce: by WP Lab – 1.8.0
Multi-Carrier Shipping Plugin for WooCommerce: by PluginHive – 1.9.9
Request a Quote for WooCommerce: by Addify – 2.3.0
WooCommerce Shipping & Tax: by WooCommerce – 1.25.20
WooCommerce: by Automattic – 6.0.0 (update to version 6.1.0 is available)
Wordfence Security: by Wordfence – 7.5.7
WordPress Importer: by wordpressdotorg – 0.7
Yoast SEO: by Team Yoast – 17.9
WP-Lister Lite for eBay: by WP Lab – 3.1.4
WP Mail SMTP Pro: by WPForms – 3.2.1
WPS Hide Login: by WPServeur
NicolasKulka
wpformation – 1.9.2

YITH WooCommerce Wishlist: by YITH – 3.5.0

### Inactive Plugins (1) ###

YITH WooCommerce Ajax Product Filter: by YITH – 4.6.0

### Dropin Plugins (1) ###

db.php: Query Monitor Database Class

### Must Use Plugins (1) ###

ManageWP - Worker Loader: by GoDaddy –

### Settings ###

API Enabled: –
Force SSL: –
Currency: USD ($)
Currency Position: left
Thousand Separator: ,
Decimal Separator: .
Number of Decimals: 2
Taxonomies: Product Types: external (external)
grouped (grouped)
simple (simple)
variable (variable)

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

Connected to WooCommerce.com: –

### WC Pages ###

Shop base: #207 - /shop/
Cart: #208 - /cart/
Checkout: #209 - /checkout/
My account: #210 - /my-account/
Terms and conditions: #74649 - /privacy-policies/

### Theme ###

Name: Irontime Sales
Version: 1.1
Author URL: https://www.portotheme.com/
Child Theme: ?
Parent Theme Name: Porto
Parent Theme Version: 6.2.2
Parent Theme Author URL: https://www.portotheme.com/
WooCommerce Support: ?

### Templates ###

Overrides: porto/woocommerce/archive-product.php
porto/woocommerce/cart/cart-empty.php
porto/woocommerce/cart/cart-shipping.php
porto/woocommerce/cart/cart-totals.php
porto/woocommerce/cart/cart.php
porto/woocommerce/cart/cross-sells.php
porto/woocommerce/cart/mini-cart.php
porto/woocommerce/cart/proceed-to-checkout-button.php
porto/woocommerce/cart/shipping-calculator.php
porto/woocommerce/checkout/form-billing.php
porto/woocommerce/checkout/form-checkout.php
porto/woocommerce/checkout/form-coupon.php
porto/woocommerce/checkout/form-login.php
porto/woocommerce/checkout/form-pay.php
porto/woocommerce/checkout/form-shipping.php
porto/woocommerce/checkout/payment-method.php
porto/woocommerce/checkout/payment.php
porto/woocommerce/checkout/review-order.php
porto/woocommerce/checkout/thankyou.php
porto/woocommerce/content-product-cat.php
porto/woocommerce/content-product.php
porto/woocommerce/content-single-product.php
porto/woocommerce/content-widget-product.php
porto/woocommerce/global/form-login.php
porto/woocommerce/global/quantity-input.php
porto/woocommerce/global/wrapper-start.php
porto/woocommerce/loop/add-to-cart.php
porto/woocommerce/loop/loop-end.php
porto/woocommerce/loop/loop-start.php
porto/woocommerce/loop/no-products-found.php
porto/woocommerce/loop/orderby.php
porto/woocommerce/loop/pagination.php
porto/woocommerce/loop/rating.php
porto/woocommerce/loop/sale-flash.php
porto/woocommerce/myaccount/dashboard.php
porto/woocommerce/myaccount/downloads.php
porto/woocommerce/myaccount/form-add-payment-method.php
porto/woocommerce/myaccount/form-edit-account.php
porto/woocommerce/myaccount/form-edit-address.php
porto/woocommerce/myaccount/form-login.php
porto/woocommerce/myaccount/form-lost-password.php
porto/woocommerce/myaccount/form-reset-password.php
porto/woocommerce/myaccount/lost-password-confirmation.php
porto/woocommerce/myaccount/my-account.php
porto/woocommerce/myaccount/my-address.php
porto/woocommerce/myaccount/my-downloads.php
porto/woocommerce/myaccount/my-orders.php
porto/woocommerce/myaccount/navigation.php
porto/woocommerce/myaccount/orders.php
porto/woocommerce/myaccount/payment-methods.php
porto/woocommerce/myaccount/view-order.php
porto/woocommerce/order/form-tracking.php
porto/woocommerce/order/order-details-customer.php
porto/woocommerce/order/order-details.php
porto/woocommerce/order/order-downloads.php
porto/woocommerce/order/tracking.php
porto/woocommerce/single-product/add-to-cart/simple.php
porto/woocommerce/single-product/add-to-cart/variable.php
porto/woocommerce/single-product/meta.php
porto/woocommerce/single-product/product-attributes.php
porto/woocommerce/single-product/product-image.php
porto/woocommerce/single-product/product-thumbnails.php
porto/woocommerce/single-product/rating.php
porto/woocommerce/single-product/related.php
porto/woocommerce/single-product/review-meta.php
porto/woocommerce/single-product/review-rating.php
porto/woocommerce/single-product/review.php
porto/woocommerce/single-product/sale-flash.php
porto/woocommerce/single-product/short-description.php
porto/woocommerce/single-product/tabs/tabs.php
porto/woocommerce/single-product/title.php
porto/woocommerce/single-product/up-sells.php
porto/woocommerce/single-product-reviews.php

### Action Scheduler ###

Complete: 2,226
Oldest: 2021-12-14 15:05:28 +0000
Newest: 2022-01-14 10:06:20 +0000

Failed: 1
Oldest: –
Newest: –

Pending: 3
Oldest: 2022-01-14 15:16:24 +0000
Newest: 2022-01-19 02:22:19 +0000

### Paytrace Gateway ###

Integration type: json
Separated Forms: No
Accept Checks: No
Allow Vault Payments: No
Require CSC: Yes
Using Encryption: No

### Status report information ###

Generated at: 2022-01-14 10:12:11 +00:00

And here are the fatal error logs:

2022-01-08T15:27:34+00:00 ERROR XML error on line 1 col 1 byte 0 Not well-formed (invalid token)
2022-01-08T15:27:34+00:00 ERROR Error: Empty Response

Hey @vdgraphics,

Thanks for sending over the system report. The amount of data can have a huge impact on the performance of searches. This site appears to have almost 58,000 products. That’s a lot of data for the search to comb through.

Have you tried running the SQL query directly in your database to see how it compares?

You may need to work with an expert on database optimization to get this running as efficiently as possible. You may want to limit some of what is searched for in the admin as a way of speeding that up.

Hi there,

It’s been a while since we last heard back from you. I’m going to mark this thread as resolved.

Hopefully, you were able to find a way forward with the issue. Feel free to start a new thread if you have any more questions.

Cheers.