Order search is very slow

  • Resolved cutu234

    (@cutu234)


    2 years, 3 months ago

    This issue has been reported several times, for example in this post.

    We have 100.000+ orders, and the search (orders table) is very slow. Even if I fill in the order ID, it can take up to 20-30 seconds for the result to appear.

    Everything in our installation is optimized. We use a super lightweight theme, and we even tested it in an installation with just the standard theme installed and Woocommerce. It didn’t make a difference. So, the problem is clearly Woocommerce itself and the way it performs the queries.

    Any news on this issue?

Viewing 9 replies - 1 through 9 (of 9 total)
  • Rynald0s

    (@rynald0s)

    Automattic Happiness Engineer

    Hi @cutu234

    The scalability of WooCommerce depends a lot on the specifications of the server you’re running it on. Realistically speaking, this is what the ongoing work around custom order tables aims to improve and which you can read about and follow along at the following links:

    https://developer.woocommerce.com/2022/01/17/the-plan-for-the-woocommerce-custom-order-table/

    https://github.com/woocommerce/woocommerce/issues/28373

    https://github.com/WordPress/performance/issues/132

    You’ll also find some interim solutions there.

    Just for curiosity sake, would you mind sharing your status report here so I can take a quick look at that?

    Cheers!

    Thread Starter cutu234

    (@cutu234)

    Hi,
    thank you for the prompt reply. We tested the database on a dedicated WordPress optimized server with no significant improvements. Traffic is usually not very high, around 1000 visits per day. So, server performance should not be the issue in our case. However, at 2.5 GB the database in pretty large.

    Here’s the status report:

    
### WordPress Environment ###

WordPress address (URL): XXXXXXXXXX
Site address (URL): XXXXXXXXXXX
WC Version: 6.8.2
REST API Version: ? 6.8.2
WC Blocks Version: ? 8.0.0
Action Scheduler Version: ? 3.4.0
Log Directory Writable: ?
WP Version: 6.0.1
WP Multisite: –
WP Memory Limit: 512 MB
WP Debug Mode: –
WP Cron: ?
Language: de_DE_formal
External object cache: –

### Server Environment ###

Server Info: Apache
PHP Version: 7.4.30
PHP Post Max Size: 200 MB
PHP Time Limit: 300
PHP Max Input Vars: 5000
cURL Version: 7.58.0
OpenSSL/1.1.1

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

### Database ###

WC Database Version: 6.8.2
WC Database Prefix: xxxxx_
Datenbank-Gesamtgr??e: 2434.34MB
Datenbank-Datengr??e: 1468.88MB
Datenbank-Indexgr??e: 965.46MB
XXXXXX_woocommerce_sessions: Daten: 3.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_woocommerce_api_keys: Daten: 0.02MB + Index: 0.03MB + Engine InnoDB
XXXXXX_woocommerce_attribute_taxonomies: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_woocommerce_downloadable_product_permissions: Daten: 0.02MB + Index: 0.06MB + Engine InnoDB
XXXXXX_woocommerce_order_items: Daten: 72.59MB + Index: 24.56MB + Engine InnoDB
XXXXXX_woocommerce_order_itemmeta: Daten: 539.98MB + Index: 394.66MB + Engine InnoDB
XXXXXX_woocommerce_tax_rates: Daten: 0.02MB + Index: 0.06MB + Engine InnoDB
XXXXXX_woocommerce_tax_rate_locations: Daten: 0.02MB + Index: 0.03MB + Engine InnoDB
XXXXXX_woocommerce_shipping_zones: Daten: 0.02MB + Index: 0.00MB + Engine InnoDB
XXXXXX_woocommerce_shipping_zone_locations: Daten: 0.02MB + Index: 0.03MB + Engine InnoDB
XXXXXX_woocommerce_shipping_zone_methods: Daten: 0.02MB + Index: 0.00MB + Engine InnoDB
XXXXXX_woocommerce_payment_tokens: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_woocommerce_payment_tokenmeta: Daten: 0.02MB + Index: 0.03MB + Engine InnoDB
XXXXXX_woocommerce_log: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_actionscheduler_actions: Daten: 3.02MB + Index: 9.13MB + Engine InnoDB
XXXXXX_actionscheduler_claims: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_actionscheduler_groups: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_actionscheduler_logs: Daten: 5.02MB + Index: 7.03MB + Engine InnoDB
XXXXXX_adtribes_my_conversions: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_aepc_custom_audiences: Daten: 0.02MB + Index: 0.00MB + Engine InnoDB
XXXXXX_aepc_logs: Daten: 5.52MB + Index: 0.00MB + Engine InnoDB
XXXXXX_aws_index: Daten: 1.52MB + Index: 1.13MB + Engine InnoDB
XXXXXX_borlabs_cookie_consent_log: Daten: 71.03MB + Index: 19.34MB + Engine MyISAM
XXXXXX_borlabs_cookie_content_blocker: Daten: 0.01MB + Index: 0.00MB + Engine MyISAM
XXXXXX_borlabs_cookie_cookies: Daten: 0.01MB + Index: 0.00MB + Engine MyISAM
XXXXXX_borlabs_cookie_groups: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_borlabs_cookie_script_blocker: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_borlabs_cookie_statistics: Daten: 4.04MB + Index: 4.64MB + Engine MyISAM
XXXXXX_bounced_email_logs: Daten: 13.52MB + Index: 0.00MB + Engine InnoDB
XXXXXX_commentmeta: Daten: 5.52MB + Index: 6.03MB + Engine InnoDB
XXXXXX_comments: Daten: 96.63MB + Index: 66.16MB + Engine InnoDB
XXXXXX_failed_jobs: Daten: 0.02MB + Index: 0.00MB + Engine InnoDB
XXXXXX_iconic_wssv_index: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_itsec_opaque_tokens: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_links: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_mapsvg6_database_475011: Daten: 0.05MB + Index: 0.02MB + Engine InnoDB
XXXXXX_mapsvg6_database_570: Daten: 0.05MB + Index: 0.02MB + Engine InnoDB
XXXXXX_mapsvg6_maps: Daten: 0.02MB + Index: 0.00MB + Engine MyISAM
XXXXXX_mapsvg6_r2o: Daten: 0.01MB + Index: 0.01MB + Engine MyISAM
XXXXXX_mapsvg6_regions_475011: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_mapsvg6_regions_570: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_mapsvg6_schema: Daten: 0.01MB + Index: 0.00MB + Engine MyISAM
XXXXXX_mapsvg6_settings: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_mapsvg_database_475011: Daten: 0.05MB + Index: 0.02MB + Engine InnoDB
XXXXXX_mapsvg_database_570: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_mapsvg_r2d: Daten: 0.00MB + Index: 0.01MB + Engine MyISAM
XXXXXX_mapsvg_regions_475011: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_mapsvg_regions_570: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_mapsvg_schema: Daten: 0.01MB + Index: 0.00MB + Engine MyISAM
XXXXXX_mclean_refs: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_mclean_scan: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_newsletter: Daten: 0.30MB + Index: 0.11MB + Engine InnoDB
XXXXXX_newsletter_emails: Daten: 0.02MB + Index: 0.00MB + Engine InnoDB
XXXXXX_newsletter_sent: Daten: 0.02MB + Index: 0.03MB + Engine InnoDB
XXXXXX_newsletter_stats: Daten: 0.02MB + Index: 0.03MB + Engine InnoDB
XXXXXX_newsletter_user_logs: Daten: 0.19MB + Index: 0.00MB + Engine InnoDB
XXXXXX_options: Daten: 7.28MB + Index: 1.41MB + Engine InnoDB
XXXXXX_postmeta: Daten: 364.91MB + Index: 273.53MB + Engine InnoDB
XXXXXX_posts: Daten: 108.56MB + Index: 52.73MB + Engine InnoDB
XXXXXX_queue: Daten: 0.02MB + Index: 0.00MB + Engine InnoDB
XXXXXX_redirection_404: Daten: 0.58MB + Index: 0.09MB + Engine MyISAM
XXXXXX_redirection_groups: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_redirection_items: Daten: 0.17MB + Index: 0.14MB + Engine MyISAM
XXXXXX_redirection_logs: Daten: 0.17MB + Index: 0.04MB + Engine MyISAM
XXXXXX_spbc_auth_logs: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_spbc_backuped_files: Daten: 0.02MB + Index: 0.00MB + Engine InnoDB
XXXXXX_spbc_backups: Daten: 0.02MB + Index: 0.00MB + Engine InnoDB
XXXXXX_spbc_firewall_data: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_spbc_firewall_logs: Daten: 0.02MB + Index: 0.00MB + Engine InnoDB
XXXXXX_spbc_scan_frontend: Daten: 0.02MB + Index: 0.00MB + Engine InnoDB
XXXXXX_spbc_scan_links_logs: Daten: 0.02MB + Index: 0.05MB + Engine InnoDB
XXXXXX_spbc_scan_results: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_spbc_scan_signatures: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_spbc_traffic_control_logs: Daten: 0.02MB + Index: 0.00MB + Engine InnoDB
XXXXXX_storeabill_documentmeta: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_storeabill_documents: Daten: 0.00MB + Index: 0.01MB + Engine MyISAM
XXXXXX_storeabill_document_itemmeta: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_storeabill_document_items: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_storeabill_document_noticemeta: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_storeabill_document_notices: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_storeabill_journals: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_sumo_tbl_user_earned_coupon: Daten: 0.02MB + Index: 0.00MB + Engine InnoDB
XXXXXX_termmeta: Daten: 0.14MB + Index: 0.11MB + Engine InnoDB
XXXXXX_terms: Daten: 0.02MB + Index: 0.03MB + Engine InnoDB
XXXXXX_term_relationships: Daten: 0.39MB + Index: 0.22MB + Engine InnoDB
XXXXXX_term_taxonomy: Daten: 0.13MB + Index: 0.05MB + Engine InnoDB
XXXXXX_updraftcentral_sitemeta: Daten: 0.02MB + Index: 0.03MB + Engine InnoDB
XXXXXX_updraftcentral_sites: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_updraftcentral_site_temporary_keys: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_updraftcentral_user_cron: Daten: 0.02MB + Index: 0.03MB + Engine InnoDB
XXXXXX_usermeta: Daten: 44.56MB + Index: 29.09MB + Engine InnoDB
XXXXXX_users: Daten: 2.52MB + Index: 1.13MB + Engine InnoDB
XXXXXX_usin_events: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_usin_user_data: Daten: 0.31MB + Index: 0.21MB + Engine MyISAM
XXXXXX_wc_admin_notes: Daten: 0.02MB + Index: 0.00MB + Engine InnoDB
XXXXXX_wc_admin_note_actions: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_wc_category_lookup: Daten: 0.02MB + Index: 0.00MB + Engine InnoDB
XXXXXX_wc_customer_lookup: Daten: 18.55MB + Index: 9.03MB + Engine InnoDB
XXXXXX_wc_download_log: Daten: 0.02MB + Index: 0.03MB + Engine InnoDB
XXXXXX_wc_gpf_render_cache: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_wc_order_coupon_lookup: Daten: 1.52MB + Index: 0.86MB + Engine InnoDB
XXXXXX_wc_order_product_lookup: Daten: 16.55MB + Index: 13.06MB + Engine InnoDB
XXXXXX_wc_order_stats: Daten: 10.52MB + Index: 7.55MB + Engine InnoDB
XXXXXX_wc_order_tax_lookup: Daten: 6.52MB + Index: 5.03MB + Engine InnoDB
XXXXXX_wc_product_attributes_lookup: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_wc_product_download_directories: Daten: 0.00MB + Index: 0.01MB + Engine MyISAM
XXXXXX_wc_product_meta_lookup: Daten: 0.02MB + Index: 0.09MB + Engine InnoDB
XXXXXX_wc_rate_limits: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_wc_reserved_stock: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_wc_tax_rate_classes: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_wc_webhooks: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_wdp_orders: Daten: 0.08MB + Index: 0.04MB + Engine MyISAM
XXXXXX_wdp_order_items: Daten: 0.05MB + Index: 0.05MB + Engine MyISAM
XXXXXX_wdp_persistent_rules_cache: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_wdp_rules: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_wfblockediplog: Daten: 0.02MB + Index: 0.00MB + Engine InnoDB
XXXXXX_wfblocks7: Daten: 0.02MB + Index: 0.05MB + Engine InnoDB
XXXXXX_wfconfig: Daten: 0.09MB + Index: 0.00MB + Engine InnoDB
XXXXXX_wfcrawlers: Daten: 0.02MB + Index: 0.00MB + Engine InnoDB
XXXXXX_wffilechanges: Daten: 0.02MB + Index: 0.00MB + Engine InnoDB
XXXXXX_wffilemods: Daten: 0.27MB + Index: 0.00MB + Engine InnoDB
XXXXXX_wfhits: Daten: 0.02MB + Index: 0.05MB + Engine InnoDB
XXXXXX_wfhoover: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_wfissues: Daten: 0.02MB + Index: 0.06MB + Engine InnoDB
XXXXXX_wfknownfilelist: Daten: 0.13MB + Index: 0.00MB + Engine InnoDB
XXXXXX_wflivetraffichuman: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_wflocs: Daten: 0.02MB + Index: 0.00MB + Engine InnoDB
XXXXXX_wflogins: Daten: 0.02MB + Index: 0.03MB + Engine InnoDB
XXXXXX_wfls_2fa_secrets: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_wfls_settings: Daten: 0.02MB + Index: 0.00MB + Engine InnoDB
XXXXXX_wfnotifications: Daten: 0.02MB + Index: 0.00MB + Engine InnoDB
XXXXXX_wfpendingissues: Daten: 0.02MB + Index: 0.06MB + Engine InnoDB
XXXXXX_wfreversecache: Daten: 0.02MB + Index: 0.00MB + Engine InnoDB
XXXXXX_wfsnipcache: Daten: 0.02MB + Index: 0.05MB + Engine InnoDB
XXXXXX_wfstatus: Daten: 5.52MB + Index: 3.03MB + Engine InnoDB
XXXXXX_wftrafficrates: Daten: 0.02MB + Index: 0.00MB + Engine InnoDB
XXXXXX_woocommerce_amazon_buyer_index: Daten: 0.02MB + Index: 0.03MB + Engine MyISAM
XXXXXX_woocommerce_gpf_google_taxonomy: Daten: 1.52MB + Index: 0.00MB + Engine InnoDB
XXXXXX_woocommerce_gzd_dhl_im_products: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_woocommerce_gzd_dhl_im_product_services: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_woocommerce_gzd_dhl_labelmeta: Daten: 0.02MB + Index: 0.03MB + Engine InnoDB
XXXXXX_woocommerce_gzd_dhl_labels: Daten: 0.02MB + Index: 0.03MB + Engine InnoDB
XXXXXX_woocommerce_gzd_packaging: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_woocommerce_gzd_packagingmeta: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_woocommerce_gzd_shipmentmeta: Daten: 20.55MB + Index: 8.03MB + Engine InnoDB
XXXXXX_woocommerce_gzd_shipments: Daten: 4.52MB + Index: 4.55MB + Engine InnoDB
XXXXXX_woocommerce_gzd_shipment_itemmeta: Daten: 12.52MB + Index: 11.03MB + Engine InnoDB
XXXXXX_woocommerce_gzd_shipment_items: Daten: 4.52MB + Index: 6.06MB + Engine InnoDB
XXXXXX_woocommerce_gzd_shipment_labelmeta: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_woocommerce_gzd_shipment_labels: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_woocommerce_gzd_shipping_provider: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_woocommerce_gzd_shipping_providermeta: Daten: 0.02MB + Index: 0.03MB + Engine InnoDB
XXXXXX_wpr_rocket_cache: Daten: 0.01MB + Index: 0.03MB + Engine MyISAM
XXXXXX_wpr_rucss_used_css: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_wt_iew_action_history: Daten: 0.01MB + Index: 0.00MB + Engine MyISAM
XXXXXX_wt_iew_mapping_template: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_yoast_indexable: Daten: 7.47MB + Index: 2.88MB + Engine InnoDB
XXXXXX_yoast_indexable_hierarchy: Daten: 0.16MB + Index: 0.20MB + Engine InnoDB
XXXXXX_yoast_migrations: Daten: 0.02MB + Index: 0.02MB + Engine InnoDB
XXXXXX_yoast_primary_term: Daten: 0.02MB + Index: 0.03MB + Engine InnoDB
XXXXXX_yoast_prominent_words: Daten: 0.00MB + Index: 0.00MB + Engine MyISAM
XXXXXX_yoast_seo_links: Daten: 0.20MB + Index: 0.13MB + Engine InnoDB
XXXXXX_yoast_seo_meta: Daten: 3.52MB + Index: 0.00MB + Engine InnoDB
_amazon_kunden: Daten: 0.62MB + Index: 0.59MB + Engine MyISAM
_rapidmail_02_2020: Daten: 0.12MB + Index: 0.08MB + Engine MyISAM

### Post Type Counts ###

acf-field: 49
acf-field-group: 11
attachment: 1709
blocks: 12
br_notice: 2
custom_css: 1
customerstory: 1
document_template: 10
gp_elements: 5
invoice: 2
jetpack_migration: 2
kundenstory: 74
mailpoet_page: 1
mapsvg: 2
mc4wp-form: 1
ml-slide: 23
ml-slider: 8
nav_menu_item: 48
oembed_cache: 25
page: 49
post: 1
product: 92
product_variation: 71
schema: 4
shop_coupon: 196
shop_order: 123572
shop_order_refund: 1802
wafs: 1
was: 15
wll_records: 1708
wp-rest-api-log: 2315
wpcf7_contact_form: 1
wpsl_stores: 64
ywrfd-discount: 1

### Security ###

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

### Active Plugins (37) ###

Advanced Custom Fields PRO: von Delicious Brains – 5.12.3
Advanced Dynamic Pricing for WooCommerce: von AlgolPlus – 4.1.3
FiboSearch - AJAX Search for WooCommerce: von FiboSearch Team – 1.19.0
Borlabs Cookie - Cookie Opt-in: von Borlabs GmbH – 2.2.54
Classic Editor: von WordPress-Mitwirkende – 1.6.2
Contact Form 7: von Takayuki Miyoshi – 5.6.2
Kundenbewertungen für WooCommerce: von CusRev – 5.3.3
GTM4WP: von Thomas Geiger – 1.16.1
GP Premium: von Tom Usborne – 2.1.2
Invisible reCaptcha: von Mihai Chelaru – 1.2.3
Rechtstexte-Schnittstelle der IT-Recht Kanzlei: von IT-Recht Kanzlei – 1.0.2
Min and Max Quantity for WooCommerce: von BeRocket – 1.3.2.4
Newsletter: von Stefano Lissa & Das Newsletter Team  – 7.5.1
Redirection: von John Godley – 5.3.2
Search By SKU - for Woocommerce: von Unroll Digital – 0.8.0
WooCommerce Show Single Variations by Iconic: von Iconic – 1.9.0
Side Cart WooCommerce: von XootiX – 2.1
Advanced Editor Tools (vormals TinyMCE Advanced): von Automattic – 5.6.0
vendidero Helper: von vendidero – 2.1.6
PayPal PLUS für WooCommerce: von Inpsyde GmbH – 2.2.1
WooCommerce Permalink Manager (Premium): von premmerce – 2.0.1
Product Feed PRO for WooCommerce: von AdTribes.io – 11.8.4
WooCommerce Advanced Shipping: von Jeroen Sormani – 1.0.14
WooSwatches Mod - Woocommerce Color or Image Variation Swatches: von woomatrix – 2.8.5 (Pixelpulli Mod)
WooCommerce Amazon Pay: von WooCommerce – 2.2.4
Germanized für WooCommerce Pro: von vendidero – 3.5.5
Germanized für WooCommerce: von vendidero – 3.10.3
heidelpay WooCommerce: von heidelpay GmbH – 1.6.0
WooCommerce Product Dependencies: von SomewhereWarm – 1.2.7
WooCommerce Price Based on Country (Basic): von Oscar Gare – 2.2.4
Sofort Banking for WooCommerce: von Sven Wagener – 1.5.1
WooCommerce Sponsor a Friend Plugin: von MB Création – 2.3.2
WooCommerce: von Automattic – 6.8.2
Yoast SEO: von Team Yoast – 19.6
WP Rocket: von WP Media – 3.12.0.3
WP Store Locator: von Tijmen Smit – 2.2.235
YITH WooCommerce Review For Discounts Premium: von YITH – 1.18.0

### Inactive Plugins (0) ###

### Dropin Plugins (1) ###

advanced-cache.php: advanced-cache.php

### Settings ###

API Enabled: ?
Force SSL: ?
Currency: EUR (€)
Currency Position: right_space
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-filtered (exclude-from-filtered)
exclude-from-search (exclude-from-search)
featured (featured)
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: ?
Enforce Approved Product Download Directories: –

### WC Pages ###

Shop-Basis: #503048 - /alle-ago-produkte/
Warenkorb: #72 - /warenkorb/
Kasse: #12 - /kasse/
Mein Konto: #70 - /mein-konto/
Allgemeine Gesch?ftsbedingungen: #688 - /allgemeine_geschaeftsbedingungen/

### Theme ###

Name: GeneratePress Child Theme
Version: 1.0
Author URL: https://my-blog-shop.de
Child Theme: ?
Parent Theme Name: GeneratePress
Parent Theme Version: 3.1.3
Parent Theme Author URL: https://tomusborne.com
WooCommerce Support: ?

### Templates ###

Overrides: generatepress_child/woocommerce/cart/cart.php
generatepress_child/woocommerce/checkout/cart-errors.php
generatepress_child/woocommerce/emails/customer-note.php
generatepress_child/woocommerce/emails/email-header.php
generatepress_child/woocommerce/emails/email-styles.php
generatepress_child/woocommerce/myaccount/view-order.php

### Geolocation debug info ###

Default customer location: geolocation
MaxMind GeoIP database: ?
MaxMind GeoIP license: ?
MM_COUNTRY_CODE: ?
GEOIP_COUNTRY_CODE: ?
HTTP_CF_IPCOUNTRY: ?
HTTP_X_COUNTRY_CODE: ?
HTTP_X_REAL_IP: 46.223.162.91
HTTP_X_FORWARDED_FOR: ?
REMOTE_ADDR: 46.223.162.91
Real external IP: 2a02:8071:3e82:9580::6592
WCPBC_USE_REMOTE_ADDR: –
Geolocation Test: ?

### PBC Settings ###

Version: 2.2.4
Base location: DE:DE-NW
Base currency: EUR
Price Based On: shipping
Shipping: –
Test mode: –
Test country: –
Load products price in background: –
Prices entered with tax: ?
Calculate tax based on: shipping
Display prices in the shop: incl

### Zone Pricing Drittl?nder ###

zone_id: drittlaender
name: Drittl?nder
countries: CH
currency: EUR
exchange_rate: 0.840336134454
auto_exchange_rate: no
disable_tax_adjustment: no

### Admin ###

Enabled Features: activity-panels
analytics
coupons
customer-effort-score-tracks
experimental-products-task
experimental-import-products-task
experimental-fashion-sample-products
experimental-product-tour
shipping-smart-defaults
shipping-setting-tour
homescreen
marketing
mobile-app-banner
navigation
onboarding
onboarding-tasks
remote-inbox-notifications
remote-free-extensions
payment-gateway-suggestions
shipping-label-banner
subscriptions
store-alerts
transient-notices
wc-pay-promotion
wc-pay-welcome-page
wc-pay-subscriptions-page

Disabled Features: minified-js
settings

Daily Cron: ? Next scheduled: 2022-08-25 10:42:20 +02:00
Options: ?
Notes: 21
Onboarding: completed

### Action Scheduler ###

Abgeschlossen: 6.226
Oldest: 2022-07-25 09:13:44 +0000
Newest: 2022-08-25 09:10:37 +0000

Fehlgeschlagen: 2
Oldest: 2022-04-02 12:42:30 +0000
Newest: 2022-04-02 12:42:30 +0000

Ausstehend: 5
Oldest: 2022-08-25 10:05:40 +0000
Newest: 2022-08-26 06:05:36 +0000

### Status report information ###

Generated at: 2022-08-25 11:13:07 +02:00

    Hello,

    There are several contributing factors to the slowness of a site, most of which can be found in the following troubleshooting documentation:

    https://docs.woocommerce.com/document/troubleshooting-a-slow-site/https://woocommerce.com/posts/woocommerce-site-slow-fixes/

    I’ve also found a similar thread. You can try the solution shared in this post to see if it helps.

    Thread Starter cutu234

    (@cutu234)

    There are several contributing factors to the slowness of a site,

    Thank you very much for your support. I know pretty good what makes a site slow. As I said in my first post: I checked the performance with the same database, but with just the necessary plugins activated and the storefont standard theme. It was pretty much the same. It is absolutely clear that Woocommerce simply does not scale up very well. With 120.000+ orders we have over 16 million rows in the postmeta table. Most of them were created by Woocommerce.

    And thanks for the link. This is exactly the thread I mentioned in my first post. ??

    • This reply was modified 2 years, 3 months ago by cutu234.
    • This reply was modified 2 years, 3 months ago by cutu234.
    • This reply was modified 2 years, 3 months ago by cutu234.

    Hi @cutu234

    Please subscribe to the GitHub threads shared above for updates on the progress in the ongoing work in this regard.

    With 120.000+ orders we have over 16 million rows in the postmeta table

    You can try installing the WP-Optimize plugin and use it to remove the transients, and then optimize the table.

    Thread Starter cutu234

    (@cutu234)

    Hi there,

    You can try installing the WP-Optimize plugin and use it to remove the transients, and then optimize the table.

    Sorry, but there are no transients in the postmeta table. ?? The wp_options is not really small with 7 million rows, but the transients contribute only around 3500 rows. So, this is negligible, I guess.

    Mirko P.

    (@rainfallnixfig)

    Hi there,

    This is mainly due to your hosting resources. You can ask your host to test an upgrade to a higher plan in a server environment with more resources.

    Popular hosts on the market have free trials so you can also export/duplicate a copy of your site in a better environment and check the result.

    If interested, the article below walks you through WooCommerce Scaling FAQs:

    https://woocommerce.com/document/woocommerce-scaling-faqs/

    Best regards.

    Thread Starter cutu234

    (@cutu234)

    This is mainly due to your hosting resources.

    Please, did you even read my second post? We tested the intallation on “high performance” WP-optimized hosting that would cost 10x as much as we pay now. If there was an effect at all, it was not significant. As I said, with around 1000 visits per days the server is far from reaching its limits. The databases run on SSDs, so there is not really very much what we can on the server side.

    Similar problems were reported by users who have the best hosting available. It is not a server issue. Again, Woocommerce simply scales not very well. While frontend performance is ok, queries of past orders are really the problem here. The statistics section is hardly usable, if you choose a larger period of time, a whole year for example. It might take minutes to load even the basic data.

    Plugin Support Kaushik S. a11n

    (@kaushiksomaiya)

    Hi there @cutu234

    As my collegue @rynald0s mentioned, you can check out those links to know more about WooCommerce and scalability.

    Large wp_postmeta table is likely the culprit here, however tweaking the server configuration can make a lot of difference.

    For example, I know a site that has 2,00,000 orders and 2.3GB in size for the table wp_postmeta alone. Despite that, the search orders by ID takes around 10-12 seconds.

    The performance may be further degraded if other plugins are adding any additional admin columns.

    From my experience, sometimes tuning MySQL parameters helps. Further, you can also explore choosing between MySQL vs MariaDB.

    I am not a DB or server expert, but I hope this points you in the right direction. ??

Viewing 9 replies - 1 through 9 (of 9 total)
  • The topic ‘Order search is very slow’ is closed to new replies.