Dynamic update of wp_wc_product_attributes_lookup not working as expected

Prerequisites

[ ] I have carried out troubleshooting steps and I believe I have found a bug.
[x] I have searched for similar bugs in both open and closed issues and cannot find a duplicate.

Describe the bug

Moving this over from the WordPress.org forums: https://wordpress.org/support/topic/issue-with-dynamic-update-of-wp_wc_product_attributes_lookup/. Originally posted by oxfordmetadata. This issue was flagged to us during Developer Office Hours, and I've asked the OP to provide any additional context they have here. I've filled out as much information as I have.

We are involved in a project where the customer opted to have 377 product attributes with 6676 product attribute terms. These exact numbers are a result of the queries.

SELECT COUNT(DISTINCT taxonomy) AS total_attributes
FROM wp_term_taxonomy
WHERE taxonomy LIKE ‘pa_%’;

SELECT COUNT(*) AS total_attribute_terms
FROM wp_terms
WHERE term_id IN (
SELECT term_id
FROM wp_term_taxonomy
WHERE taxonomy LIKE ‘pa_%’
);

The problem we are facing is that at times our product filters in the category pages were failing. This is because the product filters in order to determine which products to show on each category were utilising the wp_wc_product_attribute_look up table.

Following extensive debugging, we realised that when the lookup table we set to be updated dynamically, when the stock was changing for product with over 200 attribute terms, it was failing. It was corrupted. Here you can see such one such product with 268 variations: Aloha Ημιμόνιμο Βερνίκι Eight Color Coat 8ml – Femme Fatale (femme-fatale.gr)

I wonder what we can do in order to sort out the update and make sure that the dynamic update code is not failing.

Note that we are using a 96GB server that has less than 30% CPU utilization whereas our database has a max memory limit of 60GB and usually 45GB is available.

The site (on the front end is one of the fastest woocommerce stores ever developed – i.e. this product with the 268 variation, loads at 0.8s (Latest Performance Report for: https://femme-fatale.gr/product/nychia/imimonima-vernikia/xromata/aloha-imimonimo-verniki-eight-colo… | GTmetrix). So, I am inclined not to buy even a hint that our server could be better tuned and that responsible for the failing is our infrastructure.

Are there any plans to make the dynamic population of the wp_wc_product_attributes_lookup more robust, as nowadays, Woocommerce is becoming the platform of choice for more demanding eshops?

Also note that in our case, for 8958 products – the manual regeneration of the wp_wc_product_attributes_lookup via the tools sections takes about 2 hours.

Thank you.

Please also refer to these (partly additional) issues: Major bug in product inventory status | WordPress.org Hide out of stock items from the catalog – the case where it doesn’t work | WordPress.org

Suffices to say that all along we were taking for granted that the wp_wc_product_attributes_lookup is always correct – was our datum. We invested 100s of man hours on this effort that led to this rather unfortunate finding/conclusion.

Expected behavior

Product filters on category pages update

Actual behavior

Product filters in the category page are failing when utilitising wp_wc_product_attribute_look. That lookup table was corrupted.

Steps to reproduce

Pinged OP and asked them to provide.

WordPress Environment

Pinged OP and asked them to provide.

Isolating the problem

[ ] I have deactivated other plugins and confirmed this bug occurs when only WooCommerce plugin is active.
[ ] This bug happens with a default WordPress theme active, or Storefront.
[ ] I can reproduce this bug consistently using the steps above.

Just to clarify that the issue of wp_wc_product_attributes_lookup corruption was discovered by the Fiboseach/Fibofilters and hinted the issue during dynamic updates.

That was part of a multi-month investigation that also lead to some other intriguing thoughts about the handling of stock in variations see: https://wordpress.org/support/topic/major-bug-in-product-inventory-status

Here is the system report for our environment.

WordPress Environment

WordPress address (URL): https://femme-fatale.gr Site address (URL): https://femme-fatale.gr WC Version: 8.8.2 REST API Version: ✔ 8.8.2 Action Scheduler Version: ✔ 3.7.4 Log Directory Writable: ✔ WP Version: 6.5.2 WP Multisite: – WP Memory Limit: 8 GB WP Debug Mode: – WP Cron: – Language: el External object cache: ✔

Server Environment

Server Info: Apache/2.4.58 (Debian) PHP Version: 8.2.17 PHP Post Max Size: 512 MB PHP Time Limit: 1200 PHP Max Input Vars: 20000 cURL Version: 7.64.0 OpenSSL/1.1.1n

SUHOSIN Installed: – MySQL Version: 10.6.17-MariaDB-1:10.6.17+maria~deb10-log Max Upload Size: 500 MB Default Timezone is UTC: ✔ fsockopen/cURL: ✔ SoapClient: ✔ DOMDocument: ✔ GZip: ✔ Multibyte String: ✔ Remote Post: ✔ Remote Get: ✔

Database

WC Database Version: 8.8.2 WC Database Prefix: wp_ Total Database Size: 2679.85MB Database Data Size: 1754.93MB Database Index Size: 924.92MB wp_woocommerce_sessions: Data: 112.02MB + Index: 5.02MB + Engine InnoDB wp_woocommerce_api_keys: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_woocommerce_attribute_taxonomies: Data: 0.06MB + Index: 0.02MB + Engine InnoDB wp_woocommerce_downloadable_product_permissions: Data: 0.02MB + Index: 0.06MB + Engine InnoDB wp_woocommerce_order_items: Data: 14.52MB + Index: 3.52MB + Engine InnoDB wp_woocommerce_order_itemmeta: Data: 81.61MB + Index: 62.19MB + Engine InnoDB wp_woocommerce_tax_rates: Data: 0.02MB + Index: 0.06MB + Engine InnoDB wp_woocommerce_tax_rate_locations: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_woocommerce_shipping_zones: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_woocommerce_shipping_zone_locations: Data: 0.08MB + Index: 0.11MB + Engine InnoDB wp_woocommerce_shipping_zone_methods: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_woocommerce_payment_tokens: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_woocommerce_payment_tokenmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_woocommerce_log: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_actionscheduler_actions: Data: 25.56MB + Index: 21.30MB + Engine InnoDB wp_actionscheduler_claims: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_actionscheduler_groups: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_actionscheduler_logs: Data: 0.13MB + Index: 0.09MB + Engine InnoDB wp_ac_segments: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_admin_columns: Data: 0.05MB + Index: 0.02MB + Engine InnoDB wp_aieo_2023_09_orders: Data: 24.56MB + Index: 3.03MB + Engine InnoDB wp_aieo_2023_11_current_recommendations: Data: 1.52MB + Index: 0.00MB + Engine InnoDB wp_aieo_2023_11_orders: Data: 63.63MB + Index: 7.58MB + Engine InnoDB wp_aieo_2024_02_orders: Data: 95.72MB + Index: 10.58MB + Engine InnoDB wp_aieo_2024_03_current_recommendations: Data: 0.50MB + Index: 0.00MB + Engine InnoDB wp_aieo_2024_03_orders: Data: 103.73MB + Index: 11.58MB + Engine InnoDB wp_aieo_2024_04_current_recommendations: Data: 0.50MB + Index: 0.00MB + Engine InnoDB wp_aieo_2024_04_orders: Data: 106.72MB + Index: 11.58MB + Engine InnoDB wp_aieo_temp_orders_table: Data: 0.02MB + Index: 0.08MB + Engine InnoDB wp_aieo_temp_product_catalogue_table: Data: 6.52MB + Index: 0.75MB + Engine InnoDB wp_aioseo_cache: Data: 2.06MB + Index: 0.03MB + Engine InnoDB wp_aioseo_crawl_cleanup_blocked_args: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_aioseo_crawl_cleanup_logs: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_aioseo_links: Data: 25.55MB + Index: 0.86MB + Engine InnoDB wp_aioseo_links_suggestions: Data: 0.02MB + Index: 0.05MB + Engine InnoDB wp_aioseo_notifications: Data: 0.14MB + Index: 0.06MB + Engine InnoDB wp_aioseo_posts: Data: 55.59MB + Index: 1.52MB + Engine InnoDB wp_aioseo_redirects: Data: 7.52MB + Index: 5.27MB + Engine InnoDB wp_aioseo_redirects_404: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_aioseo_redirects_404_logs: Data: 13.52MB + Index: 3.03MB + Engine InnoDB wp_aioseo_redirects_hits: Data: 1.52MB + Index: 0.53MB + Engine InnoDB wp_aioseo_redirects_logs: Data: 8.52MB + Index: 0.88MB + Engine InnoDB wp_aioseo_revisions: Data: 19.55MB + Index: 0.38MB + Engine InnoDB wp_aioseo_search_statistics_objects: Data: 23.55MB + Index: 1.52MB + Engine InnoDB wp_aioseo_terms: Data: 1.52MB + Index: 0.16MB + Engine InnoDB wp_alphabank_transactions: Data: 0.09MB + Index: 0.00MB + Engine InnoDB wp_automatewoo_abandoned_carts: Data: 2.05MB + Index: 0.08MB + Engine InnoDB wp_automatewoo_customers: Data: 3.52MB + Index: 9.94MB + Engine InnoDB wp_automatewoo_customer_meta: Data: 1.52MB + Index: 0.72MB + Engine InnoDB wp_automatewoo_guests: Data: 1.52MB + Index: 2.41MB + Engine InnoDB wp_automatewoo_guest_meta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_automatewoo_logs: Data: 3.52MB + Index: 5.55MB + Engine InnoDB wp_automatewoo_log_meta: Data: 18.55MB + Index: 13.03MB + Engine InnoDB wp_automatewoo_queue: Data: 0.02MB + Index: 0.05MB + Engine InnoDB wp_automatewoo_queue_meta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_automatewoo_referrals: Data: 0.02MB + Index: 0.08MB + Engine InnoDB wp_automatewoo_referral_advocate_keys: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_automatewoo_referral_invites: Data: 0.02MB + Index: 0.05MB + Engine InnoDB wp_bv_fw_requests: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_bv_ip_store: Data: 0.38MB + Index: 0.20MB + Engine InnoDB wp_bv_lp_requests: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_cardlink_gateway_transactions: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_cli_cookie_scan: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_cli_cookie_scan_categories: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_cli_cookie_scan_cookies: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_cli_cookie_scan_url: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_cli_scripts: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_commentmeta: Data: 0.02MB + Index: 0.06MB + Engine InnoDB wp_comments: Data: 45.59MB + Index: 35.16MB + Engine InnoDB wp_cs_proof_consent: Data: 24.55MB + Index: 0.00MB + Engine InnoDB wp_cs_scan: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_cs_scan_cookies: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_cs_scan_scripts: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_cs_stats_consent: Data: 4.52MB + Index: 0.00MB + Engine InnoDB wp_cs_unblock_ip: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_dgwt_wcas_index: Data: 3.52MB + Index: 0.19MB + Engine InnoDB wp_dgwt_wcas_invindex_cache: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_dgwt_wcas_invindex_doclist: Data: 32.56MB + Index: 42.11MB + Engine InnoDB wp_dgwt_wcas_invindex_wordlist: Data: 3.52MB + Index: 4.52MB + Engine InnoDB wp_dgwt_wcas_stats: Data: 4.02MB + Index: 0.00MB + Engine InnoDB wp_dgwt_wcas_tax_index: Data: 0.19MB + Index: 0.05MB + Engine InnoDB wp_dgwt_wcas_var_index: Data: 3.52MB + Index: 0.72MB + Engine InnoDB wp_events: Data: 29.55MB + Index: 0.00MB + Engine InnoDB wp_fibofilters_descriptors_main: Data: 1.52MB + Index: 0.14MB + Engine InnoDB wp_fibofilters_doclist_main: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_fibofilters_sources_main: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_fibofilters_valuelist_main: Data: 0.14MB + Index: 0.06MB + Engine InnoDB wp_gf_addon_feed: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_gf_draft_submissions: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_gf_entry: Data: 1.52MB + Index: 0.22MB + Engine InnoDB wp_gf_entry_meta: Data: 4.52MB + Index: 7.58MB + Engine InnoDB wp_gf_entry_notes: Data: 1.52MB + Index: 0.16MB + Engine InnoDB wp_gf_form: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_gf_form_meta: Data: 1.52MB + Index: 0.00MB + Engine InnoDB wp_gf_form_revisions: Data: 1.50MB + Index: 0.03MB + Engine InnoDB wp_gf_form_view: Data: 0.30MB + Index: 0.27MB + Engine InnoDB wp_gf_rest_api_keys: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_gla_budget_recommendations: Data: 0.20MB + Index: 0.11MB + Engine InnoDB wp_gla_merchant_issues: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_gla_shipping_rates: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_gla_shipping_times: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_links: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_masterslider_options: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_masterslider_sliders: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_mo_openid_linked_user: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_namastecon_activities: Data: 1.52MB + Index: 0.00MB + Engine InnoDB wp_namastecon_comments: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_namastecon_maillog: Data: 0.06MB + Index: 0.00MB + Engine InnoDB wp_namastecon_newsletters: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_namastepro_badges: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_namastepro_bundles: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_namastepro_classes: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_namastepro_class_managers: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_namastepro_coupons: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_namastepro_delayed_access: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_namastepro_files: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_namastepro_school_signups: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_namastepro_school_students: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_namastepro_shopcarts: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_namastepro_student_classes: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_namaste_certificates: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_namaste_course_reviews: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_namaste_history: Data: 1.52MB + Index: 0.00MB + Engine InnoDB wp_namaste_homeworks: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_namaste_homework_notes: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_namaste_payments: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_namaste_solution_files: Data: 1.50MB + Index: 0.00MB + Engine InnoDB wp_namaste_student_certificates: Data: 0.05MB + Index: 0.00MB + Engine InnoDB wp_namaste_student_courses: Data: 0.17MB + Index: 0.00MB + Engine InnoDB wp_namaste_student_homeworks: Data: 0.06MB + Index: 0.00MB + Engine InnoDB wp_namaste_student_lessons: Data: 0.16MB + Index: 0.00MB + Engine InnoDB wp_namaste_student_modules: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_namaste_visits: Data: 0.20MB + Index: 0.00MB + Engine InnoDB wp_namaste_webhooks: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_options: Data: 10.50MB + Index: 0.91MB + Engine InnoDB wp_podsrel: Data: 0.45MB + Index: 1.03MB + Engine InnoDB wp_pods_cssurvey: Data: 0.52MB + Index: 0.00MB + Engine InnoDB wp_pods_cssurveypd: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_pods_new_stock: Data: 0.05MB + Index: 0.00MB + Engine InnoDB wp_pods_optimal_suggestion: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_pods_order_products_analytics: Data: 27.56MB + Index: 5.52MB + Engine InnoDB wp_pods_order_stats: Data: 14.55MB + Index: 1.52MB + Engine InnoDB wp_pods_parent_product_cat_hierarchy: Data: 1.52MB + Index: 0.34MB + Engine InnoDB wp_pods_pa_brand_master: Data: 0.28MB + Index: 0.00MB + Engine InnoDB wp_pods_post: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_pods_product_brands: Data: 1.52MB + Index: 1.95MB + Engine InnoDB wp_pods_product_cat: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_pods_product_vitals: Data: 14.55MB + Index: 4.38MB + Engine InnoDB wp_pods_reciprocal_product: Data: 0.38MB + Index: 0.00MB + Engine InnoDB wp_pods_user: Data: 0.50MB + Index: 0.00MB + Engine InnoDB wp_pods_variation_vitals: Data: 1.52MB + Index: 1.19MB + Engine InnoDB wp_postmeta: Data: 257.95MB + Index: 247.70MB + Engine InnoDB wp_posts: Data: 59.48MB + Index: 16.02MB + Engine InnoDB wp_po_plugins: Data: 56.55MB + Index: 7.78MB + Engine InnoDB wp_pys_stat_landing: Data: 1.52MB + Index: 0.00MB + Engine InnoDB wp_pys_stat_order: Data: 1.52MB + Index: 0.00MB + Engine InnoDB wp_pys_stat_product_order: Data: 4.52MB + Index: 0.00MB + Engine InnoDB wp_pys_stat_traffic: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_pys_stat_utm_campaing: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_pys_stat_utm_content: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_pys_stat_utm_medium: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_pys_stat_utm_source: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_pys_stat_utm_term: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_s1erp_reciprocal_ids_table: Data: 1.52MB + Index: 1.64MB + Engine InnoDB wp_s1erp_temp_customers_table: Data: 0.02MB + Index: 0.06MB + Engine InnoDB wp_s1erp_temp_orders_table: Data: 0.02MB + Index: 0.08MB + Engine InnoDB wp_s1erp_temp_partial_customers_table: Data: 0.44MB + Index: 0.33MB + Engine InnoDB wp_s1erp_temp_partial_product_catalogue_table: Data: 0.02MB + Index: 0.05MB + Engine InnoDB wp_s1erp_temp_product_catalogue_table: Data: 1.52MB + Index: 1.47MB + Engine InnoDB wp_s1erp_temp_store_orders_table: Data: 0.02MB + Index: 0.11MB + Engine InnoDB wp_sgs_log_events: Data: 0.30MB + Index: 0.17MB + Engine InnoDB wp_sgs_log_visitors: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_signups: Data: 0.02MB + Index: 0.06MB + Engine InnoDB wp_termmeta: Data: 4.45MB + Index: 8.73MB + Engine InnoDB wp_terms: Data: 1.52MB + Index: 0.78MB + Engine InnoDB wp_term_relationships: Data: 5.44MB + Index: 3.30MB + Engine InnoDB wp_term_taxonomy: Data: 1.52MB + Index: 0.78MB + Engine InnoDB wp_tm_taskmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_tm_tasks: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_usermeta: Data: 54.66MB + Index: 135.53MB + Engine InnoDB wp_users: Data: 3.52MB + Index: 6.06MB + Engine InnoDB wp_vivawallet_data: Data: 0.34MB + Index: 0.00MB + Engine InnoDB wp_wcpdf_invoice_number: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_wc_admin_notes: Data: 0.06MB + Index: 0.00MB + Engine InnoDB wp_wc_admin_note_actions: Data: 0.05MB + Index: 0.02MB + Engine InnoDB wp_wc_category_lookup: Data: 0.06MB + Index: 0.00MB + Engine InnoDB wp_wc_customer_lookup: Data: 4.52MB + Index: 4.00MB + Engine InnoDB wp_wc_download_log: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_wc_orders: Data: 11.52MB + Index: 19.00MB + Engine InnoDB wp_wc_orders_meta: Data: 75.61MB + Index: 110.88MB + Engine InnoDB wp_wc_order_addresses: Data: 11.52MB + Index: 14.05MB + Engine InnoDB wp_wc_order_composite_lookup: Data: 0.02MB + Index: 0.09MB + Engine InnoDB wp_wc_order_coupon_lookup: Data: 0.08MB + Index: 0.11MB + Engine InnoDB wp_wc_order_operational_data: Data: 7.52MB + Index: 4.03MB + Engine InnoDB wp_wc_order_product_lookup: Data: 10.52MB + Index: 13.03MB + Engine InnoDB wp_wc_order_stats: Data: 5.52MB + Index: 5.52MB + Engine InnoDB wp_wc_order_tax_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_wc_product_attributes_lookup: Data: 4.52MB + Index: 2.52MB + Engine InnoDB wp_wc_product_download_directories: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_wc_product_meta_lookup: Data: 3.52MB + Index: 4.78MB + Engine InnoDB wp_wc_rate_limits: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_wc_reserved_stock: Data: 0.08MB + Index: 0.00MB + Engine InnoDB wp_wc_tax_rate_classes: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_wc_webhooks: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_wiz_cummulative_order_stats: Data: 11.52MB + Index: 2.52MB + Engine InnoDB wp_wiz_order_products_entry: Data: 7.52MB + Index: 2.52MB + Engine InnoDB wp_wiz_order_products_exit: Data: 7.52MB + Index: 1.52MB + Engine InnoDB wp_wiz_order_products_freq_entry: Data: 2.52MB + Index: 0.11MB + Engine InnoDB wp_wiz_order_products_freq_exit: Data: 3.52MB + Index: 0.13MB + Engine InnoDB wp_wiz_order_products_freq_single_item_orders: Data: 1.52MB + Index: 0.08MB + Engine InnoDB wp_wiz_order_products_single_orders: Data: 3.52MB + Index: 1.52MB + Engine InnoDB wp_wiz_order_stats: Data: 14.55MB + Index: 1.52MB + Engine InnoDB wp_wiz_product_brands: Data: 1.52MB + Index: 0.30MB + Engine InnoDB wp_woobe_history: Data: 0.02MB + Index: 0.05MB + Engine InnoDB wp_woobe_history_bulk: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_woocommerce_orders: Data: 8.52MB + Index: 2.31MB + Engine InnoDB wp_woocommerce_pickup_locations_geodata: Data: 0.02MB + Index: 0.05MB + Engine InnoDB wp_woocommerce_recommendations: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_woocommerce_session_activity: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_woocommerce_shipping_table_rates: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_woot_tables: Data: 0.05MB + Index: 0.00MB + Engine InnoDB wp_woot_tables_columns: Data: 0.05MB + Index: 0.03MB + Engine InnoDB wp_woot_tables_meta: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_woot_vocabulary: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_wpdatacharts: Data: 0.05MB + Index: 0.00MB + Engine InnoDB wp_wpdatatables: Data: 0.05MB + Index: 0.00MB + Engine InnoDB wp_wpdatatables_cache: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_wpdatatables_columns: Data: 0.23MB + Index: 0.00MB + Engine InnoDB wp_wpdatatables_folders: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_wpdatatables_folders_meta: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_wpdatatables_rows: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_wpdatatables_templates: Data: 0.50MB + Index: 0.00MB + Engine InnoDB wp_wpfm_backup: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_wpforms_tasks_meta: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_wpmailsmtp_debug_events: Data: 1.52MB + Index: 0.00MB + Engine InnoDB wp_wpmailsmtp_emails_log: Data: 9.52MB + Index: 1.67MB + Engine InnoDB wp_wpmailsmtp_email_tracking_events: Data: 1.52MB + Index: 0.86MB + Engine InnoDB wp_wpmailsmtp_email_tracking_links: Data: 10.52MB + Index: 1.52MB + Engine InnoDB wp_wpmailsmtp_tasks_meta: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_wpr_rocket_cache: Data: 2.02MB + Index: 4.23MB + Engine InnoDB wp_wpr_rucss_used_css: Data: 1.52MB + Index: 2.14MB + Engine InnoDB wp_yith_wcan_filter_sessions: Data: 0.02MB + Index: 0.05MB + Engine InnoDB

Post Type Counts

_pods_field: 306 _pods_group: 17 _pods_pod: 17 attachment: 30788 aw_workflow: 20 consentmagic: 3 cookielawinfo: 6 cpt_variation_fields: 1 cs-template: 4 custom_css: 4 faq: 3 fgf_rules: 4 gfw_event: 2 gfw_report: 532 gift_card: 1 mailpoet_page: 1 mgmlp_media_folder: 95 namaste_course: 17 namaste_homework: 4 namaste_lesson: 28 nav_menu_item: 735 oembed_cache: 5 page: 78 plugin_filter: 4 post: 192 product: 7867 product_variation: 7490 pys_event: 6 saved_reply: 2 shop_coupon: 202 shop_order_placehold: 8975 shop_order_refund: 6 sp_wps_shortcodes: 3 tdb_templates: 24 tds_locker: 1 ticket: 17 waitlist_subscribers: 89 wc_pickup_location: 4 wcpf_item: 8 wcpf_project: 1 wishlist: 4 wp_block: 5 wp_global_styles: 2 wp_navigation: 1 wpcode: 2 wpforms: 2 yith_wcan_preset: 1 yith_wcps_type: 3

Security

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

Active Plugins (84)

Disable Bloat for WordPress & WooCommerce: by Disable Bloat – 3.4.8 Gravity Forms: by Gravity Forms – 2.8.7 weLaunch Framework: by weLaunch.io – 1.1.1 AI eshop Optimizer: by Oxford Metadata Ltd – 1.0 AIOSEO - Image SEO: by All in One SEO Team – 1.1.11 AIOSEO - IndexNow: by All in One SEO Team – 1.0.11 AIOSEO - Local Business: by All in One SEO Team – 1.3.1 FiboSearch - AJAX Search for WooCommerce (Pro): by FiboSearch Team – 1.27.1.13 All in One SEO Pro: by All in One SEO Team – 4.6.1 aThemes Blocks: by aThemes – 1.0.10 aThemes Starter Sites: by aThemes – 1.0.52 AutoConvert Greeklish Permalinks: by Dimitris Mavroudis – 4.1.2 AutomateWoo - Birthdays Add-on: by WooCommerce – 1.3.34 AutomateWoo - Refer A Friend Add-on: by WooCommerce – 2.7.17 AutomateWoo: by WooCommerce – 6.0.20 BestPrice 360º: by BestPrice – 1.1.2 Botiga Pro: by aThemes – 1.3.7 BOX NOW Delivery: by BOX NOW – 2.1 Change Date Language (English speakers): by Marco Foggia – 0.1.1 ConsentMagic Pro: by ConsentMagic – 3.2.0 Yoast Duplicate Post: by Enrico Battocchi & Team Yoast – 4.5 Exit Intent Offer: by Oxford Metadata Ltd – 1.0 Femme Fatale Orders Customization: by Dimitris Vayenas – 1.0 Femme Fatale Recommendations: by Dimitris Vayenas – 1.0 FiboFilters: by FiboFilters Team – 1.1.0.14 Site Kit by Google: by Google – 1.124.0 Gravity Forms Polls Add-On: by Gravity Forms – 4.2.0 Gravity Forms Survey Add-On: by Gravity Forms – 4.0.0 WooCommerce Custom Fields for Variations by Iconic: by Iconic – 1.4.0 Index WP MySQL For Speed: by Oliver Jones Rick James – 1.4.17

Loco Translate: by Tim Whitlock – 2.6.7 Max Mega Menu - Pro Addon: by megamenu.com – 2.4 Max Mega Menu: by megamenu.com – 3.3.1 Members: by MemberPress – 3.2.9 Merchant Pro: by aThemes – 1.9.7 Merchant: by aThemes – 1.9.7 Woocommerce Meta Connector: by Oxford Metadata Ltd – 1.0 Namaste! Connect: by Kiboko Labs – 1.0.7 Namaste! LMS: by Kiboko Labs – 2.6.1.3 Namaste! PRO: by Kiboko Labs – 1.5.6 Object Cache Pro: by Rhubarb Group – 1.20.2 PDF Bridge: by Kiboko Labs – 1.9 Performant Translations: by WordPress Performance Team – 1.2.0 PixelYourSite PRO: by PixelYourSite – 10.1.2 PixelYourSite Super Pack: by PixelYourSite – 5.0.4 Plugin Organizer: by Jeff Sterup – 10.1.10 Pods Gravity Forms Add-On: by Pods Framework Team – 1.5.0 Pods Pro - Commerce Toolkit Add-On: by SKC Development LLC – 1.0.1

Pods - Custom Content Types and Fields: by Pods Framework Team – 3.2.1 Post Types Order: by Nsp Code – 2.2.1 Preserve Page and Taxonomy Hierarchy on Edit Menus Screen: by Sergey Biryukov – 0.1 Product Catalog Feed Pro by PixelYourSite: by PixelYourSite – 5.4.2 Repeat Order for Woocommerce: by polyres – 1.3.3 Security Optimizer: by SiteGround – 1.4.13 Skroutz Analytics for WooCommerce: by Skroutz – 1.7.3 Category Order and Taxonomy Terms Order: by Nsp-Code – 1.8.1 TC Custom JavaScript: by Tiny Code – 1.2.3 Timologia for WooCommerce: by John Athanasiou – 5.0.1 Mobile Detect: by Pothi Kalimuthu – 1.5.1 UpdraftPlus - Backup/Restore: by UpdraftPlus.Com DavidAnderson – 2.24.2.26

Visual Term Description Editor: by Shea Bunge – 1.8.1 Viva Wallet Smart Checkout: by Viva Wallet – 3.6.8 Master-Detail Tables for wpDataTables: by TMS-Plugins – 1.3.7 Powerful Filters for wpDataTables: by TMS-Plugins – 1.4.4 WooCommerce UPC, EAN, and ISBN: by Scott Bolinger – 0.5.1 BEAR – Bulk Editor and Products Manager Professional for WooCommerce: by realmag777 – 2.1.4 Product Slider Pro for WooCommerce: by ShapedPlugin – 3.3.1 Woocommerce Advanced Categories: by weLaunch – 1.2.20 WooCommerce PayPal Payments: by WooCommerce – 2.6.1 PDF Invoices & Packing Slips for WooCommerce: by WP Overnight – 3.8.0 WooCommerce Product Manual Sorting Performance Improvement: by Hayden Whiteman – WooCommerce Shipment Tracking: by WooCommerce – 2.4.7 WooCommerce Shop the Look: by weLaunch – 1.0.9 WooCommerce Social Login: by SkyVerge – 2.15.1 WooCommerce Table Rate Shipping: by WooCommerce – 3.1.9 WooCommerce Ultimate Pricing: by weLaunch – 1.1.7 WooCommerce: by Automattic – 8.8.2 WOOT - WooCommerce Active Products Tables: by realmag777 – 2.0.6 WP Mail SMTP Pro: by WP Mail SMTP – 3.11.1 WP Rocket - D-bugger: by WP Rocket Support Team – 1.2.1 WP Rocket | Remove HTML Expires from .htaccess rules: by WP Rocket Support Team – WP Rocket | Change Remove Unused CSS Parameters: by WP Rocket Support Team – WP Rocket: by WP Media – 3.15.10 wpDataTables: by TMS-Plugins – 6.3.1

Inactive Plugins (16)

AIOSEO - News Sitemap: by All in One SEO Team – 1.0.15 AIOSEO - Redirects: by All in One SEO Team – 1.4.0 AIOSEO - REST API: by All in One SEO Team – 1.0.6 Better Search Replace: by WP Engine – 1.4.6 Better Search Replace Pro: by Delicious Brains – 1.4.3 CookieYes | GDPR Cookie Consent: by CookieYes – 3.2.1 Femme Fatale Exporter: by Dimitris Vayenas – 2.0 Gift Wrapper Plus: by Sagehen Studio – 5.3.0 Gravity Forms User Registration Add-On: by Gravity Forms – 5.2.0 GTmetrix for WordPress: by GTmetrix – 0.4.8 Mouseflow for Wordpress: by Mouseflow – 5.1.3 Regenerate Thumbnails: by Alex Mills (Viper007Bond) – 3.1.6 ReOrder Posts within Categories: by Aurorata V. – 2.14.5 User Role Editor: by Vladimir Garagulya – 4.64.2 WooCommerce Composite Products: by WooCommerce – 8.10.3 WP Snow - Best Snow Effect Plugin: by Ironikus – 1.0.3

Dropin Plugins ()

advanced-cache.php: advanced-cache.php object-cache.php: Object Cache Pro (Drop-in)

Must Use Plugins (3)

Index WP MySQL For Speed Upgrade Filter for mu-plugins.: by – 1.4.10 Plugin Organizer MU: by Jeff Sterup – 10.1.10 prevent-table.php: by –

Settings

API Enabled: – Force SSL: – Currency: EUR (€) Currency Position: right_space Thousand Separator: . Decimal Separator: , Number of Decimals: 2 Taxonomies: Product Types: composite (composite) 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) 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: – HPOS feature screen enabled: ✔ HPOS feature enabled: ✔ Order datastore: Automattic\WooCommerce\Internal\DataStores\Orders\OrdersTableDataStore HPOS data sync enabled: –

Logging

Enabled: ✔ Handler: Automattic\WooCommerce\Internal\Admin\Logging\LogHandlerFileV2 Retention period: 30 days Level threshold: – Log directory size: 461 KB

WC Pages

Shop base: #29019 - /shop Cart: #29049 - /cart Checkout: #29050 - /checkout My account: #29051 - /my-account Terms and conditions: #29755 - /terms

Theme

Name: FemmeFatale V2 Version: 11.4 Author URL: https://about.gr Child Theme: ✔ Parent Theme Name: Botiga Parent Theme Version: 2.2.2 Parent Theme Author URL: https://athemes.com WooCommerce Support: ✔

Templates

Overrides: femme-fatale-V2/woocommerce/checkout/thankyou.php /home/850962.cloudwaysapps.com/qsdtxkdjvv/public_html/wp-content/plugins/woocommerce/templates/content-product-cat.php

WooCommerce PayPal Payments

Onboarded: ✔ Shop country code: GR WooCommerce currency supported: ✔ Advanced Card Processing available in country: ✔ Pay Later messaging available in country: – Webhook status: – PayPal Vault enabled: – ACDC Vault enabled: – Logging enabled: – Reference Transactions: – Used PayPal Checkout plugin: – Subscriptions Mode: Disabled

Admin

Enabled Features: activity-panels analytics product-block-editor coupons core-profiler customize-store customer-effort-score-tracks import-products-task experimental-fashion-sample-products shipping-smart-defaults shipping-setting-tour homescreen marketing mobile-app-banner navigation onboarding onboarding-tasks product-variation-management product-virtual-downloadable product-external-affiliate product-grouped product-linked product-pre-publish-modal remote-inbox-notifications remote-free-extensions payment-gateway-suggestions shipping-label-banner subscriptions store-alerts transient-notices woo-mobile-welcome wc-pay-promotion wc-pay-welcome-page

Disabled Features: minified-js new-product-management-experience product-custom-fields settings async-product-editor-category-field launch-your-store

Daily Cron: ✔ Next scheduled: 2024-04-18 09:05:25 +02:00 Options: ✔ Notes: 74 Onboarding: completed

Social Login

Facebook: ✔ Available Twitter: Disabled Google: ✔ Available Amazon: Disabled Linkedin: Disabled Paypal: Disabled Disqus: Disabled Vkontakte: Disabled

Action Scheduler

Complete: 55,364 Oldest: 2024-04-14 18:35:50 +0000 Newest: 2024-04-17 18:34:31 +0000

Failed: 3,967 Oldest: 2023-11-27 19:15:41 +0000 Newest: 2024-04-17 14:01:56 +0000

Pending: 72 Oldest: 2024-04-17 18:35:06 +0000 Newest: 2024-04-24 12:39:55 +0000

Status report information

Generated at: 2024-04-17 20:35:29 +02:00 `

In the thread I have added the recommended approach to expedite/facilitate the generation of the table.

Basically, if categories and other other attributes are needed, have to be computed first. Then only the simple products (using the categories/meta info previously computed). Then only the variations (using the categories/meta info previously computed) And then the combination to one table.

Failure to do so, leads to at least three more joins in the SQL that multiply the total time needed by two orders of magnitude.

I take that the team as it uses the Woocommerce objects (and all their properties) it falls into this trap; thus resulting in huge delays and/or the corruption we observed during dynamic updates in large installations, particularly if the system make intensive use of REST API (via WP Rocket' and/or automate scheduled jobs).

I know that it is not a best practice to go for direct operations to the database, but our php/woocommerce skills are not on par with our experience in quering databases.

We can also confirm that the php code, apart from performance, may also have concurrency issues. i.e. in a test bed, with single user, with no multiple stock changes, the table - during dynamic update - may not get corrupted. But if during its dynamic update operation, the stock (of other products) changes then it will definetely get corrupted.

We tested that with products of over 200 variations of image type.

Reported here: 8052316-zen

Hi, I'm Néstor Soriano, the developer who implemented the WooCommerce product attributes lookup table feature.

It's clear that I failed to design and test the feature for high volume stores and that the current approach for databse querying is inappropriate. I'll do some research and work on improving the performance of the current code and queries, taking in account the suggestion posted in https://wordpress.org/support/topic/issue-with-dynamic-update-of-wp_wc_product_attributes_lookup/#post-17693855 (thanks for that!). I'll report progress here.

Sorry for all the trouble and thanks for all the context provided.

I responded to your note in the thread (because it was more appropriate there (considering my comments before)).

Here is the "wrong' SQL code that takes 15-20 minutes vs the 7 seconds that the I one mentioned there - it is only for reference, but it is telling of the issue that you were facing.

`TRUNCATE TABLE wp_pods_product_brands; INSERT INTO wp_pods_product_brands (name, product_id, parent_product_id, effective_product_id, brand_name, product_stock, product_count) SELECT t.name AS name, CASE WHEN p.id is NULL THEN tr.object_id ELSE p.id END AS product_id, tr.object_id AS parent_product_id, tr.object_id AS effective_product_id, t.slug as brand_name, pm.meta_value as product_stock, (SELECT count(ts.slug) FROM wp_term_relationships AS trs
INNER JOIN wp_term_taxonomy AS xs ON (xs.taxonomy='pa_brand_master' AND xs.term_taxonomy_id=trs.term_taxonomy_id AND tr.term_taxonomy_id=trs.term_taxonomy_id) INNER JOIN wp_terms AS ts ON ts.term_id=xs.term_id group by ts.slug limit 1) as product_count
FROM wp_term_relationships AS tr
INNER JOIN wp_term_taxonomy AS x ON (x.taxonomy='pa_brand_master' AND x.term_taxonomy_id=tr.term_taxonomy_id) INNER JOIN wp_terms AS t ON t.term_id=x.term_id JOIN wp_posts p on (tr.object_id=p.post_parent or tr.object_id=p.id) AND p.post_type like '%product%' AND p.post_status='publish' JOIN wp_postmeta pm ON tr.object_id=pm.post_id AND meta_key='_stock'
order by product_id, name desc LIMIT 5000000;

TRUNCATE TABLE wp_pods_product_vitals; INSERT INTO wp_pods_product_vitals (
image_url, name, product_id, parent_id, parent_name, parent_sku, category_id, brand, menu_id, menu_category, head_id, head_category, subcat_id, sub_category, sku, gtin, soft1_id, profdiscount, price, product_url, link_to_view, link_to_cart, link_to_fav, short_description, weight, color, stock ) SELECT distinct CASE WHEN p.post_parent=0 THEN concat('https://femme-fatale.gr/wp-content/uploads/',pm2.meta_value) ELSE
concat('https://femme-fatale.gr/wp-content/uploads/',pm8.meta_value)
END AS image_url, p.post_title as name, p.ID as product_id,

CASE WHEN p.post_parent=0 THEN p.id ELSE
pmmp.post_parent END AS parent_id,

CASE WHEN p.post_parent=0 THEN p.post_title ELSE
pmmpp.post_title END AS parent_name,

CASE WHEN p.post_parent=0 THEN pm4.meta_value ELSE
pmmpps.meta_value END AS parent_sku,

-- tr.object_id as product_id, t.term_id as category_id, CASE WHEN p.post_parent=0 THEN tat.name ELSE
tatv.name
END AS brand,

t2.term_id as menu_id, t2.name as menu_category, t.term_id as head_id, t.name as head_category,

CASE WHEN p.post_parent=0 THEN ( CASE WHEN
(select count() from wp_term_taxonomy ttc join wp_term_relationships trc ON ttc.term_taxonomy_id = trc.term_taxonomy_id AND ttc.taxonomy='product_cat' AND
trc.object_id=p.id)=2 THEN t3.term_id ELSE
(SELECT
tz.term_id FROM wp_terms tz JOIN wp_term_taxonomy ttz ON tz.term_id = ttz.term_id JOIN wp_term_relationships wprz ON wprz.term_taxonomy_id = ttz.term_taxonomy_id LEFT JOIN wp_term_taxonomy tt2z ON ttz.parent = tt2z.term_id AND tt2z.taxonomy = ttz.taxonomy AND ttz.taxonomy IN ('product_cat') AND ttz.taxonomy != 'category' JOIN wp_terms t2z ON tt2z.term_id=t2z.term_id LEFT JOIN wp_term_taxonomy tt3z ON tt2z.parent = tt3z.term_id AND tt2z.taxonomy = tt3z.taxonomy JOIN wp_terms t3z ON tt3z.term_id=t3z.term_id INNER JOIN wp_term_relationships AS trz ON trz.term_taxonomy_id = ttz.term_taxonomy_id INNER JOIN wp_posts AS pz ON trz.object_id=pz.id WHERE pz.id =p.id AND pz.post_type like '%product%' group by trz.object_id ORDER BY t3z.name, t2z.name LIMIT 1 ) END) -- if it is a variation THEN do search based ON parent ELSE
( CASE WHEN
(select count() from wp_term_taxonomy ttc join wp_term_relationships trc ON ttc.term_taxonomy_id = trc.term_taxonomy_id AND ttc.taxonomy='product_cat' AND
trc.object_id=pmmp.post_parent)=2 THEN t3.term_id ELSE
(SELECT
tz.term_id FROM wp_terms tz JOIN wp_term_taxonomy ttz ON tz.term_id = ttz.term_id JOIN wp_term_relationships wprz ON wprz.term_taxonomy_id = ttz.term_taxonomy_id LEFT JOIN wp_term_taxonomy tt2z ON ttz.parent = tt2z.term_id AND tt2z.taxonomy = ttz.taxonomy AND ttz.taxonomy IN ('product_cat') AND ttz.taxonomy != 'category' JOIN wp_terms t2z ON tt2z.term_id=t2z.term_id LEFT JOIN wp_term_taxonomy tt3z ON tt2z.parent = tt3z.term_id AND tt2z.taxonomy = tt3z.taxonomy JOIN wp_terms t3z ON tt3z.term_id=t3z.term_id INNER JOIN wp_term_relationships AS trz ON trz.term_taxonomy_id = ttz.term_taxonomy_id INNER JOIN wp_posts AS pz ON trz.object_id=pz.id WHERE pz.id =pmmp.post_parent AND pz.post_type like '%product%' group by trz.object_id ORDER BY t3z.name, t2z.name LIMIT 1 ) END)

END as subcat_id,

CASE WHEN p.post_parent=0 THEN ( CASE WHEN
(select count(*) from wp_term_taxonomy ttc join wp_term_relationships trc ON ttc.term_taxonomy_id = trc.term_taxonomy_id AND ttc.taxonomy='product_cat' AND
trc.object_id=p.id)=2 THEN t3.name ELSE
(SELECT
tz.name FROM wp_terms tz JOIN wp_term_taxonomy ttz ON tz.term_id = ttz.term_id JOIN wp_term_relationships wprz ON wprz.term_taxonomy_id = ttz.term_taxonomy_id AND ttz.taxonomy IN ('product_cat') AND ttz.taxonomy != 'category' LEFT JOIN wp_term_taxonomy tt2z ON ttz.parent = tt2z.term_id AND tt2z.taxonomy = ttz.taxonomy JOIN wp_terms t2z ON tt2z.term_id=t2z.term_id LEFT JOIN wp_term_taxonomy tt3z ON tt2z.parent = tt3z.term_id AND tt2z.taxonomy = tt3z.taxonomy JOIN wp_terms t3z ON tt3z.term_id=t3z.term_id INNER JOIN wp_term_relationships AS trz ON trz.term_taxonomy_id = ttz.term_taxonomy_id INNER JOIN wp_posts AS pz ON trz.object_id=pz.id WHERE pz.id =p.id AND pz.post_type like '%product%'
group by trz.object_id ORDER BY t3z.name, t2z.name LIMIT 1 ) END)

-- if it is a variation THEN ELSE ( CASE WHEN
(select count(*) from wp_term_taxonomy ttc join wp_term_relationships trc ON ttc.term_taxonomy_id = trc.term_taxonomy_id AND ttc.taxonomy='product_cat' AND
trc.object_id=pmmp.post_parent)=2 THEN t3.name ELSE
(SELECT
tz.name FROM wp_terms tz JOIN wp_term_taxonomy ttz ON tz.term_id = ttz.term_id JOIN wp_term_relationships wprz ON wprz.term_taxonomy_id = ttz.term_taxonomy_id AND ttz.taxonomy IN ('product_cat') AND ttz.taxonomy != 'category' LEFT JOIN wp_term_taxonomy tt2z ON ttz.parent = tt2z.term_id AND tt2z.taxonomy = ttz.taxonomy JOIN wp_terms t2z ON tt2z.term_id=t2z.term_id LEFT JOIN wp_term_taxonomy tt3z ON tt2z.parent = tt3z.term_id AND tt2z.taxonomy = tt3z.taxonomy JOIN wp_terms t3z ON tt3z.term_id=t3z.term_id INNER JOIN wp_term_relationships AS trz ON trz.term_taxonomy_id = ttz.term_taxonomy_id INNER JOIN wp_posts AS pz ON trz.object_id=pz.id WHERE pz.id =pmmp.post_parent AND pz.post_type like '%product%'
group by trz.object_id ORDER BY t3z.name, t2z.name LIMIT 1 ) END)

END as sub_category,

pm4.meta_value as sku,

CASE WHEN p.post_parent=0 THEN pm0.meta_value ELSE
pmmppb.meta_value
END AS gtin, pm9.meta_value as soft1_id, (SELECT CASE WHEN EXISTS ( SELECT 1 FROM wp_postmeta pm6 WHERE pm6.meta_key = 'woocommerce_ultimate_pricing_prices' AND pm6.post_id = p.id AND pm6.metavalue LIKE '%customer%' LIMIT 1 ) THEN 'NAI' ELSE 'OXI' END ) AS ProfDiscount,

CAST(REPLACE(pm3.meta_value, ',', '.') AS DECIMAL(12,2)) as price, CASE WHEN p.post_parent=0 THEN concat ("https://femme-fatale.gr/?p=", p.id) ELSE
concat ("https://femme-fatale.gr/?p=", pmmpp.ID) END AS product_url, Concat('', ("Αναλυτική περιγραφή" COLLATE utf8mb3_bin) ,'') as link_to_view,

CASE WHEN p.post_parent=0 AND pm5.meta_value>0 THEN Concat('

<a href="?add-to-cart=', tr.object_id , '" data-quantity="1" class="button product_type_simple add_to_cart_button ajax_add_to_cart" data-product_id="', tr.object_id, ' data-product_sku="', pm4.meta_value , '" rel="nofollow">', ("Αγορά" COLLATE utf8mb3_bin) , '

') when p.post_parent<>0 AND pm14.meta_value >0 THEN Concat('

') ELSE '' END

as link_to_cart, '''' as link_to_fav,

CASE WHEN p.post_parent=0 THEN pm11.meta_value ELSE
pm13.meta_value END AS short_description,

CASE WHEN p.post_parent=0 THEN pm10.meta_value ELSE
pm12.meta_value END AS weight,

CASE WHEN p.post_parent=0 THEN '' ELSE
trim(replace (replace(p.post_title, pmmpp.post_title, ''), '- ', '') ) END AS color,

CASE WHEN p.post_parent=0 THEN pm5.meta_value ELSE
pm14.meta_value END AS stock FROM wp_posts as p

LEFT JOIN wp_posts pmmp ON pmmp.ID=p.id -- now get the parent of the variation so that you can get the url of the parent LEFT JOIN wp_posts pmmpp ON pmmpp.ID=pmmp.post_parent -- now get the parent of sku of the parent LEFT JOIN wp_postmeta AS pmmpps ON pmmpp.ID=pmmpps.post_id AND pmmpps.meta_key='_sku' LEFT JOIN wp_postmeta AS pmmppb ON pmmp.ID=pmmppb.post_id AND pmmppb.meta_key='hwp_var_gtin' LEFT JOIN wp_postmeta AS pm7 ON p.ID = pm7.post_id AND pm7.meta_key = 'botiga_variation_gallery' -- Get the value of the attachment LEFT JOIN wp_postmeta AS pm8 ON substring_index(substring_index(pm7.meta_value, ':"', -1), '";', 1) = pm8.post_id AND pm8.meta_key = '_wp_attached_file' LEFT JOIN wp_pods_product_brands as tatv ON pmmpp.ID = tatv.parent_product_id

LEFT JOIN wp_term_relationships AS tr ON tr.object_id=p.id or tr.object_id=pmmp.post_parent LEFT JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy IN ('product_cat') AND tt.taxonomy != 'category' JOIN wp_terms t ON tt.term_id=t.term_id LEFT JOIN wp_term_taxonomy tt2 ON tt.parent = tt2.term_id AND tt2.taxonomy = tt.taxonomy JOIN wp_terms t2 ON tt2.term_id=t2.term_id LEFT JOIN wp_term_taxonomy tt3 ON tt3.parent = tt2.term_id AND tt3.taxonomy = tt2.taxonomy AND tt3.term_taxonomy_id=tt2.term_taxonomy_id

LEFT JOIN wp_terms t3 ON tt3.term_id=t3.term_id LEFT JOIN wp_postmeta AS pm0 ON p.id = pm0.post_id AND pm0.meta_key = 'hwp_product_gtin' LEFT JOIN wp_postmeta AS pm1 ON p.id = pm1.post_id AND pm1.meta_key = '_thumbnail_id' LEFT JOIN wp_postmeta AS pm2 ON pm1.meta_value = pm2.post_id AND pm2.meta_key = '_wp_attached_file' LEFT JOIN wp_postmeta AS pm3 ON p.id = pm3.post_id AND pm3.meta_key = '_price' LEFT JOIN wp_postmeta AS pm4 ON p.id = pm4.post_id AND pm4.meta_key = '_sku' LEFT JOIN wp_postmeta AS pm5 ON p.id = pm5.post_id AND pm5.meta_key = '_stock' LEFT JOIN wp_postmeta AS pm6 ON p.id = pm6.post_id AND pm6.meta_key = 'woocommerce_ultimate_pricing_prices' LEFT JOIN wp_postmeta AS pm9 ON p.id = pm9.post_id AND pm9.meta_key = 'soft1_id' LEFT JOIN wp_postmeta AS pm11 ON p.id = pm11.post_id AND pm11.meta_key = '_aioseo_description' LEFT JOIN wp_postmeta AS pm10 ON p.id= pm10.post_id AND pm10.meta_key = '_weight' LEFT JOIN wp_postmeta AS pm13 ON pmmpp.ID = pm13.post_id AND pm13.meta_key = '_aioseo_description' LEFT JOIN wp_postmeta AS pm12 ON pmmpp.ID = pm12.post_id AND pm12.meta_key = '_weight' LEFT JOIN wp_postmeta AS pm14 ON pmmp.ID = pm14.post_id AND pm14.meta_key = '_stock' LEFT JOIN wp_pods_product_brands as tat ON p.id = tat.product_id

WHERE p.post_type IN ('product', 'product_variation') AND p.post_status = 'publish' -- statement to ensure uniqueness of product ids group by p.post_title ORDER BY p.id, t2.name LIMIT 5000000;`

The very tricky bits - apart from the obvious almost 30 JOINS in the end are the following

the fact that out of those 30 JOINS about 10 are variation/simple product related i.e. because if it a variation do something else than what you are doing for the simple.
Because the result is common we have to have the lovely case when parent = 0 then... else... end in most of the selects (loading further the processes)
The computation of hierarchies in this context... e.g. check (and forget :) ) the computation of a sub-sub-category

... CASE WHEN p.post_parent=0 THEN ( CASE WHEN (select count(*) from wp_term_taxonomy ttc join wp_term_relationships trc ON ttc.term_taxonomy_id = trc.term_taxonomy_id AND ttc.taxonomy='product_cat' AND trc.object_id=p.id)=2 THEN t3.name ELSE (SELECT tz.name FROM wp_terms tz JOIN wp_term_taxonomy ttz ON tz.term_id = ttz.term_id JOIN wp_term_relationships wprz ON wprz.term_taxonomy_id = ttz.term_taxonomy_id AND ttz.taxonomy IN ('product_cat') AND ttz.taxonomy != 'category' LEFT JOIN wp_term_taxonomy tt2z ON ttz.parent = tt2z.term_id AND tt2z.taxonomy = ttz.taxonomy JOIN wp_terms t2z ON tt2z.term_id=t2z.term_id LEFT JOIN wp_term_taxonomy tt3z ON tt2z.parent = tt3z.term_id AND tt2z.taxonomy = tt3z.taxonomy JOIN wp_terms t3z ON tt3z.term_id=t3z.term_id INNER JOIN wp_term_relationships AS trz ON trz.term_taxonomy_id = ttz.term_taxonomy_id INNER JOINwp_posts` AS pz ON trz.object_id=pz.id WHERE pz.id =p.id AND pz.post_type like '%product%'
group by trz.object_id ORDER BY t3z.name, t2z.name LIMIT 1 ) END)

END as sub_category ... `

Should I have included these 8 x (take your pick) JOINS in the 30 JOINs end-join it would have chocked any mySQL server - even this one that resides in this kit: https://pcpartpicker.com/b/dT7TwP - never mind your average hosts mySQL.

What I try to say is that given the unfortunate architecture of the distinction between variable and simple products your task @Konamiman was more like 'mission impossible' me thinks.

As we know there are different meta for these two type of products - never mind the stock (what another awful conception to have a stock in the parent variable product as if it is simple (which is an illogical conception as the parent product is merely an abstraction has no reciprocal in stock (as we have argued with @shameemreza )))!

Until the product architecture changes, I see no other option than to treat separately the simple products from the variations. I could make nothing to work and when I did got these result.

One could argue that it ought to be difficult to do the switch over to another architecture in the core of the products. Just think this: Inversion of scope. Instead of having the parent as a simple product and its variations as special cases, to turn the model upside down; have the variation values as those of a simple product and the variations parent to be a special case. This would have solved the issue in no time. But I guess this is a core - core issue of woocommerce that cannot be up to your task in dealing with the attributes of all types of products as they stand today.

The issue of categories (at least to three levels) is a sine qua non these days and it sad that the wordpress architecture basically inherently supports just two. Never mind 3 special attributes that ought to be global given the need of the customers (size, basic colour (at least one) and brands (most customers - end users I mean - give emphasis to the brands, se we ought to do the same)).

I think you had enough of me by now. If you need anything just let me know.

The offer to provide you with our dataset/access to our beta testing server is always on the table.

Keep up the good work

Regardless of the performance fix that @Konamiman may produce, a refactoring of the products (and customers) tables - similar to the excellent work done with HPOS re the orders postmeta - is inevitable.

I take for granted that all people interested in this thread already know how magento and open cart store their products.

Coincidentally, these days it came to my attention a .net based open source e-commerce platform called nop commerce. First time I had heard this platform so I thought it will be good to share here their approach on how they deal with products (simple and variations and stock availability).

Here is their product table. Too broad for my liking but I am marking in bold the elements that I feel we ought to have in a potential dedicated product table of woocommerce.

CREATE TABLE product ( Id int NOT NULL AUTO_INCREMENT, Name varchar(400) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL, MetaKeywords varchar(400) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, MetaTitle varchar(400) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, Sku varchar(400) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, ManufacturerPartNumber varchar(400) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, Gtin varchar(400) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, RequiredProductIds varchar(1000) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, AllowedQuantities varchar(1000) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, ProductTypeId int NOT NULL, ParentGroupedProductId int NOT NULL, VisibleIndividually tinyint(1) NOT NULL, ShortDescription longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci, FullDescription longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci, AdminComment longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci, ProductTemplateId int NOT NULL, VendorId int NOT NULL, ShowOnHomepage tinyint(1) NOT NULL, MetaDescription longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci, AllowCustomerReviews tinyint(1) NOT NULL, ApprovedRatingSum int NOT NULL, NotApprovedRatingSum int NOT NULL, ApprovedTotalReviews int NOT NULL, NotApprovedTotalReviews int NOT NULL, SubjectToAcl tinyint(1) NOT NULL, LimitedToStores tinyint(1) NOT NULL, IsGiftCard tinyint(1) NOT NULL, GiftCardTypeId int NOT NULL, OverriddenGiftCardAmount decimal(18,4) DEFAULT NULL, RequireOtherProducts tinyint(1) NOT NULL, AutomaticallyAddRequiredProducts tinyint(1) NOT NULL, IsDownload tinyint(1) NOT NULL, DownloadId int NOT NULL, UnlimitedDownloads tinyint(1) NOT NULL, MaxNumberOfDownloads int NOT NULL, DownloadExpirationDays int DEFAULT NULL, DownloadActivationTypeId int NOT NULL, HasSampleDownload tinyint(1) NOT NULL, SampleDownloadId int NOT NULL, HasUserAgreement tinyint(1) NOT NULL, UserAgreementText longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci, IsRecurring tinyint(1) NOT NULL, RecurringCycleLength int NOT NULL, RecurringCyclePeriodId int NOT NULL, RecurringTotalCycles int NOT NULL, IsRental tinyint(1) NOT NULL, RentalPriceLength int NOT NULL, RentalPricePeriodId int NOT NULL, IsShipEnabled tinyint(1) NOT NULL, IsFreeShipping tinyint(1) NOT NULL, ShipSeparately tinyint(1) NOT NULL, AdditionalShippingCharge decimal(18,4) NOT NULL, DeliveryDateId int NOT NULL, IsTaxExempt tinyint(1) NOT NULL, TaxCategoryId int NOT NULL, IsTelecommunicationsOrBroadcastingOrElectronicServices tinyint(1) NOT NULL, ManageInventoryMethodId int NOT NULL, ProductAvailabilityRangeId int NOT NULL, UseMultipleWarehouses tinyint(1) NOT NULL, WarehouseId int NOT NULL, StockQuantity int NOT NULL, DisplayStockAvailability tinyint(1) NOT NULL, DisplayStockQuantity tinyint(1) NOT NULL, MinStockQuantity int NOT NULL, LowStockActivityId int NOT NULL, NotifyAdminForQuantityBelow int NOT NULL, BackorderModeId int NOT NULL, AllowBackInStockSubscriptions tinyint(1) NOT NULL, OrderMinimumQuantity int NOT NULL, OrderMaximumQuantity int NOT NULL, AllowAddingOnlyExistingAttributeCombinations tinyint(1) NOT NULL, NotReturnable tinyint(1) NOT NULL, DisableBuyButton tinyint(1) NOT NULL, DisableWishlistButton tinyint(1) NOT NULL, AvailableForPreOrder tinyint(1) NOT NULL, PreOrderAvailabilityStartDateTimeUtc datetime(6) DEFAULT NULL, CallForPrice tinyint(1) NOT NULL, Price decimal(18,4) NOT NULL, OldPrice decimal(18,4) NOT NULL, ProductCost decimal(18,4) NOT NULL, CustomerEntersPrice tinyint(1) NOT NULL, MinimumCustomerEnteredPrice decimal(18,4) NOT NULL, MaximumCustomerEnteredPrice decimal(18,4) NOT NULL, BasepriceEnabled tinyint(1) NOT NULL, BasepriceAmount decimal(18,4) NOT NULL, BasepriceUnitId int NOT NULL, BasepriceBaseAmount decimal(18,4) NOT NULL, BasepriceBaseUnitId int NOT NULL, MarkAsNew tinyint(1) NOT NULL, MarkAsNewStartDateTimeUtc datetime(6) DEFAULT NULL, MarkAsNewEndDateTimeUtc datetime(6) DEFAULT NULL, HasTierPrices tinyint(1) NOT NULL, HasDiscountsApplied tinyint(1) NOT NULL, Weight decimal(18,4) NOT NULL, Length decimal(18,4) NOT NULL, Width decimal(18,4) NOT NULL, Height decimal(18,4) NOT NULL, AvailableStartDateTimeUtc datetime(6) DEFAULT NULL, AvailableEndDateTimeUtc datetime(6) DEFAULT NULL, DisplayOrder int NOT NULL, Published tinyint(1) NOT NULL, Deleted tinyint(1) NOT NULL, CreatedOnUtc datetime(6) NOT NULL, UpdatedOnUtc datetime(6) NOT NULL, Material varchar(1024) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, ModelWears varchar(1024) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, SizeGuideId int NOT NULL DEFAULT '0', ShowProductCollectionArea tinyint(1) NOT NULL DEFAULT '0', ERPSync_Code varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, PRIMARY KEY (Id), KEY IX_Product_VisibleIndividually_Published_Deleted_Extended (VisibleIndividually,Published,Deleted), KEY IX_Product_VisibleIndividually (VisibleIndividually), KEY IX_Product_SubjectToAcl (SubjectToAcl), KEY IX_Product_ShowOnHomepage (ShowOnHomepage), KEY IX_Product_Published (Published), KEY IX_Product_PriceDatesEtc (Price,AvailableStartDateTimeUtc,AvailableEndDateTimeUtc,Published,Deleted), KEY IX_Product_ParentGroupedProductId (ParentGroupedProductId), KEY IX_Product_LimitedToStores (LimitedToStores), KEY IX_Product_Delete_Id (Deleted,Id), KEY IX_Product_Deleted_and_Published (Published,Deleted), KEY IX_GetLowStockProducts (Deleted,VendorId,ProductTypeId,ManageInventoryMethodId,MinStockQuantity,UseMultipleWarehouses) ) ENGINE=InnoDB AUTO_INCREMENT=22307 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I may have skipped a few. But the point is that, basically, all the vital product data (i.e. the data that marketplaces demand from the merchants (with a few exceptions such as images/image galleries) these days) ought to be accommodated in a single table.

In their approach, variations are just simple products (e.g. products of the same product type) that are connected with their parent (that has a different product type) via the ParentGroupedProductId.

One more parameter that I would like to highlight is the differences in performance between the various flavours of MySQL.

I have modified the first script (the one that creates the "temp" table regarding the brands attribute) and added to it size (with up to 3 different attributes for size) and colour (with up to 3 different attributes for colour)

INSERT INTO wp_aieo_core_product_attributes (name, product_id, parent_product_id, effective_product_id, brand_name, product_stock, product_count, size, color) SELECT t.name AS name, CASE WHEN p.id IS NULL THEN tr.object_id ELSE p.id END AS product_id, tr.object_id AS parent_product_id, tr.object_id AS effective_product_id, t.slug as brand_name, '0' as product_stock,
'0' as product_count, MAX(ts.name) as size, MAX(tc.name) as color FROM wp_posts p JOIN wp_term_relationships tr ON (p.id = tr.object_id OR p.post_parent = tr.object_id) AND p.post_type LIKE '%product%' AND p.post_status = 'publish' JOIN wp_term_taxonomy xt ON xt.term_taxonomy_id = tr.term_taxonomy_id AND xt.taxonomy = 'pa_brand_master' JOIN wp_terms t ON t.term_id = xt.term_id LEFT JOIN (SELECT trs.object_id, ts.name FROM wp_term_relationships trs JOIN wp_term_taxonomy xs ON xs.term_taxonomy_id = trs.term_taxonomy_id JOIN wp_terms ts ON ts.term_id = xs.term_id WHERE xs.taxonomy IN ('pa_size', 'pa_megethos', 'pa_noumero')) AS ts ON p.id = ts.object_id LEFT JOIN (SELECT trc.object_id, tc.name FROM wp_term_relationships trc JOIN wp_term_taxonomy xc ON xc.term_taxonomy_id = trc.term_taxonomy_id JOIN wp_terms tc ON tc.term_id = xc.term_id WHERE xc.taxonomy IN ('pa_color', 'pa_chroma', 'pa_colour')) AS tc ON p.id = tc.object_id GROUP BY p.id, tr.object_id, t.name, t.slug ORDER BY p.id, t.slug DESC;

Here is the interesting bit: When this is executed on

My laptops MySQL 8.0.36 (native) installation it takes about 2.5seconds.
My laptop's MariaDB 10.4.32 (via Local) it takes 83.5 seconds.
Siteground's MySQL 5.7.44-48 installation it takes about 0.82seconds
Cloudway's MariaDB 10.6.17 installation it takes 73.8 seconds

As this is a typical query (for Wordpress/Woocommerce installations) i.e. extremely simple schemas with simple joins of up to 5 tables, I would suggest the Automattic DB experts to engage with MariaDB engineers to figure out what is going on with their implementation as it has such a grave impact in our ecosystem. If anything else to congratulate them for managing to be 100 times slower than their MySQL counterparts for one of our most popular queries; it is worthy of an explanation - similar to the braking technology in the auto business :)

Can you post the explains from each?

MySQL 8.0.39 (Native Local) - (EXPLAIN ANALYZE)

'-> Insert into wp_aieo_core_product_attributes\n -> Sort: p.ID, t.slug DESC (actual time=1890..1892 rows=14958 loops=1)\n -> Table scan on (actual time=1823..1827 rows=14958 loops=1)\n -> Aggregate using temporary table (actual time=1823..1823 rows=14958 loops=1)\n -> Nested loop left join (cost=12.8e+6 rows=3.84e+6) (actual time=0.402..1725 rows=15054 loops=1)\n -> Nested loop left join (cost=12.4e+6 rows=998817) (actual time=0.397..1197 rows=14958 loops=1)\n -> Nested loop inner join (cost=12.3e+6 rows=259963) (actual time=0.384..623 rows=14958 loops=1)\n -> Nested loop inner join (cost=548 rows=2356) (actual time=0.0856..31 rows=7920 loops=1)\n -> Nested loop inner join (cost=178 rows=254) (actual time=0.0709..5.32 rows=254 loops=1)\n -> Index lookup on xt using taxonomy (taxonomy=\'pa_brand_master\') (cost=88.9 rows=254) (actual time=0.0571..1.91 rows=254 loops=1)\n -> Single-row index lookup on t using PRIMARY (term_id=xt.term_id) (cost=0.25 rows=1) (actual time=0.0129..0.0129 rows=1 loops=254)\n -> Covering index lookup on tr using term_taxonomy_id (term_taxonomy_id=xt.term_taxonomy_id) (cost=0.532 rows=9.28) (actual time=0.00701..0.0969 rows=31.2 loops=254)\n -> Filter: (((p.ID = tr.object_id) or (p.post_parent = tr.object_id)) and (p.post_type like \'%product%\') and (p.post_status = \'publish\')) (cost=357 rows=110) (actual time=0.0654..0.0742 rows=1.89 loops=7920)\n -> Index range scan on p (re-planned for each iteration) (cost=357 rows=52274) (actual time=0.0548..0.069 rows=3.82 loops=7920)\n -> Nested loop inner join (cost=2.94 rows=3.84) (actual time=0.0379..0.0379 rows=0 loops=14958)\n -> Nested loop inner join (cost=1.6 rows=3.84) (actual time=0.0376..0.0376 rows=0 loops=14958)\n -> Covering index lookup on trs using PRIMARY (object_id=p.ID) (cost=0.253 rows=3.84) (actual time=0.00512..0.0158 rows=4.92 loops=14958)\n -> Filter: (xs.taxonomy in (\'pa_size\',\'pa_megethos\',\'pa_noumero\')) (cost=117e-6 rows=1) (actual time=0.00417..0.00417 rows=0 loops=73659)\n -> Single-row index lookup on xs using PRIMARY (term_taxonomy_id=trs.term_taxonomy_id) (cost=117e-6 rows=1) (actual time=0.00329..0.00335 rows=1 loops=73659)\n -> Single-row index lookup on ts using PRIMARY (term_id=xs.term_id) (cost=117e-6 rows=1) (never executed)\n -> Nested loop inner join (cost=2.94 rows=3.84) (actual time=0.0347..0.0348 rows=0.0101 loops=14958)\n -> Nested loop inner join (cost=1.6 rows=3.84) (actual time=0.0345..0.0346 rows=0.0101 loops=14958)\n -> Covering index lookup on trc using PRIMARY (object_id=p.ID) (cost=0.253 rows=3.84) (actual time=0.00383..0.0137 rows=4.92 loops=14958)\n -> Filter: (xc.taxonomy in (\'pa_color\',\'pa_chroma\',\'pa_colour\')) (cost=30.5e-6 rows=1) (actual time=0.00398..0.00398 rows=0.00205 loops=73659)\n -> Single-row index lookup on xc using PRIMARY (term_taxonomy_id=trc.term_taxonomy_id) (cost=30.5e-6 rows=1) (actual time=0.00315..0.0032 rows=1 loops=73659)\n -> Single-row index lookup on tc using PRIMARY (term_id=xc.term_id) (cost=30.5e-6 rows=1) (actual time=0.0049..0.00496 rows=1 loops=151)\n'

Local MariaDB 10.4.32 (via Flywheel Local) (EXPLAIN EXTENDED)

1 SIMPLE p index PRIMARY,post_parent post_parent 172 41478 100.00 Using where; Using index; Using temporary; Using filesort 1 SIMPLE trs ref PRIMARY,term_taxonomy_id PRIMARY 8 local.p.ID 1 100.00 Using where; Using index 1 SIMPLE xs eq_ref PRIMARY,term_id_taxonomy,taxonomy PRIMARY 8 local.trs.term_taxonomy_id 1 0.04 Using where 1 SIMPLE ts eq_ref PRIMARY PRIMARY 8 local.xs.term_id 1 100.00
1 SIMPLE xt ref PRIMARY,term_id_taxonomy,taxonomy taxonomy 130 const 254 100.00 Using index condition 1 SIMPLE t eq_ref PRIMARY PRIMARY 8 local.xt.term_id 1 100.00
1 SIMPLE trc ref PRIMARY,term_taxonomy_id PRIMARY 8 local.p.ID 1 100.00 Using where; Using index 1 SIMPLE xc eq_ref PRIMARY,term_id_taxonomy,taxonomy PRIMARY 8 local.trc.term_taxonomy_id 1 0.33 Using where 1 SIMPLE tc eq_ref PRIMARY PRIMARY 8 local.xc.term_id 1 100.00
1 SIMPLE tr ref PRIMARY,term_taxonomy_id term_taxonomy_id 8 local.xt.term_taxonomy_id 6 100.00 Using where; Using index

With SHOW PROFILE we are getting this:

Starting 0.000165 Checking permissions 0.000010 Opening tables 0.000046 After opening tables 0.000008 System lock 0.000005 Table lock 0.000008 Init 0.000125 Optimizing 0.000040 Statistics 0.000202 Preparing 0.000044 Creating tmp table 0.000022 Sorting result 0.000012 Executing 0.000002 Sending data 13.669791 Converting HEAP to Aria 0.023119 Creating index 0.000048 Repair by sorting 0.001534 Saving state 0.000055 Creating index 0.000004 Converting HEAP to Aria 0.000090 Sending data 20.805226 Creating sort index 0.008694 Removing tmp table 0.001338 Creating sort index 0.000031 End of update loop 0.000010 Query end 0.000003 Commit 0.000005 Closing tables 0.000005 Removing tmp table 0.000004 Closing tables 0.000002 Removing tmp table 0.000003 Closing tables 0.000003 Unlocking tables 0.000002 Closing tables 0.000012 Starting cleanup 0.000002 Freeing items 0.000021 Updating status 0.000060 Reset for next command 0.000018

(So there is a lot of time Sending Data)

Siteground MySQL 5.7

1	INSERT	wp_aieo_core_product_attributes	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
1	SIMPLE	t	NULL	ALL	PRIMARY	NULL	NULL	NULL	1	100.00	Using temporary; Using filesort
1	SIMPLE	xt	NULL	eq_ref	PRIMARY,term_id_taxonomy,taxonomy	term_id_taxonomy	138	dbnfatrecif0qe.t.term_id,const	1	100.00	Using index
1	SIMPLE	tr	NULL	ref	PRIMARY,term_taxonomy_id	term_taxonomy_id	8	dbnfatrecif0qe.xt.term_taxonomy_id	15	100.00	Using index
1	SIMPLE	p	NULL	ALL	PRIMARY,post_parent	NULL	NULL	NULL	48865	0.21	Range checked for each record (index map: 0x5)
1	SIMPLE	xs	NULL	index	PRIMARY,term_id_taxonomy,taxonomy	term_id_taxonomy	138	NULL	1	100.00	Using where; Using index
1	SIMPLE	ts	NULL	eq_ref	PRIMARY	PRIMARY	8	dbnfatrecif0qe.xs.term_id	1	100.00	NULL
1	SIMPLE	trs	NULL	eq_ref	PRIMARY,term_taxonomy_id	PRIMARY	16	dbnfatrecif0qe.p.ID,dbnfatrecif0qe.xs.term_taxonom...	1	100.00	Using index
1	SIMPLE	xc	NULL	index	PRIMARY,term_id_taxonomy,taxonomy	term_id_taxonomy	138	NULL	1	100.00	Using where; Using index
1	SIMPLE	tc	NULL	eq_ref	PRIMARY	PRIMARY	8	dbnfatrecif0qe.xc.term_id	1	100.00	NULL
1	SIMPLE	trc	NULL	eq_ref	PRIMARY,term_taxonomy_id	PRIMARY	16	dbnfatrecif0qe.p.ID,dbnfatrecif0qe.xc.term_taxonom...	1	100.00	Using index

Profiling is disabled at SiteGround's production environment

Cloudflare's MariaDB 10.6.17

Query executed OK, 14,693 rows affected. (102.458 s)

SHOW PROFILE FOR QUERY 1 Status Duration Starting 0.000046 Query end 0.000009 Commit 0.000007 closing tables 0.000006 Starting cleanup 0.000006 Freeing items 0.000008 Updating status 0.000019 Reset for next command 0.000007

Please find here a dataset that contains all the tables implicated (with this rather complex dataset of about 400 attributes with 7000 attribute terms) and a wp_postmeta table of about 1 million rows.

BenchmarkDump.zip

To further facilitate the discussion (because we also have the reverse effect in another query where MariaDB is 5-6 times faster than MySQL) - i.e. in the last query MariaDB executes it in less than 3-4 seconds, whereas it takes MySQL 15-20seconds.

Also note that I am still experimenting with the queries as I try to optimize the order of the joins etc. :)

Also included are the queries are queries that I am using. sql-scripts.zip

Here is the execution times in MySQL 5.7 installation at Siteground.

The respective times in MariaDB 10.4.32 (on local)

Hi @oxfordmetadata, I have now some time to dedicate to this. Yes, having a dump of yor dataset would be very useful in order to start by trying to reproduce the issue myself, thank you.

Great news. Yes, this is why I added them in here so that they can act as a datum.

With respect to the MariaDB (vs MySQL) performance issues, I was talking with one of the leading MS SQL DB Admins, who informed me that similar behaviour does occur on MS SQL (over a certain size of dataset) and that in one particular case he had to move the tables to another partition (having a size 3 times the size of the data implicated in the joins of query) for it to perform.

Possibly MariaDB experts can inform if we can tweak any of their default settings to improve performance (i.e. join_buffer_size etc.) .

My initial plan once I have a large dataset for testing is to try and implement an alternative way to regenerate the lookup table, based on direct access to the posts table instead of using the WooCommerce data access objects (as suggested in the pull request description), and compare it with the current regeneration code in terms of performance.

@Konamiman The dump I shared with you previously is not good enough?

@oxfordmetadata Sorry, I'm juggling so many things at the same time that I end up missing the basics. That should be good indeed, many thanks!

The MariaDB team also asked to open a case on their Jira and see what happens.

Here is the ticket opened on MariaDB's Jira: https://jira.mariadb.org/browse/MDEV-34097

Also here is the peformance in MS SQL - (following migration of the BenchmarkDump to MS SQL

So sub-second performance is being observed, similar to MySQL 5.7

What happens when you convert the INSERT into a native SELECT? In other words, just return the data you want to insert. How long does that take on the problematic MariaDB's?

Also, are you using any replication, Galera, etc? Just curious on the second point.

Not much! The cost is in the Select not on the insert. Please refer to the updated thread in here: https://jira.mariadb.org/browse/MDEV-34097?page=com.atlassian.jira.plugin.system.issuetabpanels%3Aall-tabpanel I was told it has been brought to the attention of Monty so lets see what happens...

Yea, well one thing I've avoided using for several years now, especially for busy tables are the inserts from a select:

INSERT INTO a SELECT FROM b;

This has always been due to the replication locking issue (regardless of if you are replicating). Tease me and test just the select. If it's a second or less, that might be it.

The other thing, as you are seeing in the ticket is the optimizer settings, designed to help that may be doing the opposite. The optimizer got a big refresh again in 11.x.

I am testing just the select :) I had the same inquiry from Daniel who commented on the thread and said that first I am writing the select (as I add/remove various columns and play with the sequence of various joins and used the insert in this form for my convenience. Sadly 99% of the cost is on the select e.g. say from 8 seconds the select is 7.9seconds so to speak.

No, the issue is on the internal memory handling while creating the dynamic arrays for the matrices in the dynamic query. There is obviously some inefficient code aka "bug" in there. This explains the variance even between the various flavours of MariaDB. See the analysis for the version 11.3.2 vs 11.3.3 never mind the other versions.

Please contribute on the Jira for this bit, because this thread actually is about another issue - that is just been made worse due to MariaDB. But it is not like that if MariaDB sort this issue, this issue will be resolved.

One more thing - in case you havent't read our post in Wordpress support. You may notice in the scripts that I am using 5 steps to do this computation e.g. deliver the product vitals for simple and variations in a flat table - with a multitidue of key characteristics. When you try to get all this information in one select you require dozens of joins and the time it used to take was about 15 minutes. I mean we experimented a lot to minimise the likelyhood of not just "locks" but interlocks... and this may explain why when separating simple products from variations we get such a boost in performance.

In my private discussion with Daniel I was also saying that in the stored procedure version of this code, the problematic left joins had this form

LEFT JOIN
                      (SELECT trs.object_id, ts.name FROM wp_term_relationships trs
                       JOIN wp_term_taxonomy xs ON xs.term_taxonomy_id = trs.term_taxonomy_id
                       JOIN wp_terms ts ON ts.term_id = xs.term_id
                       WHERE FIND_IN_SET(xs.taxonomy, sizeTaxonomies) > 0) AS ts ON p.id = ts.object_id

And initially I was thinking that had to do with FIND_IN_SET. But it was not to be. So it is something far more fundamental.

Well, I'm really looking forward to the fix then. I'll subscribe to the ticket for sure.

@Konamiman Please check this amazing breakthrough we had in MariaDB

https://jira.mariadb.org/browse/MDEV-34097?page=com.atlassian.jira.plugin.system.issuetabpanels%3Aall-tabpanel

You may recall that I went down from 15 minutes to 10-15 seconds by opting to treat differently simple and variable products.

In the MariaDB thread you will see how we can reach sub second results. This is due to the way that OR (which is used to cover for both product_id and their parents (to cover for both simple and plain products in the initial stage that its impact I had thought to be limited)) misbehaves - as the number of NULL rows effectively disables any optimization in the execution plan.

The UNION solution for now is the way to go. It could take MariaDB a while, and the performance you got with the UNION was exceptional everywhere.

Glad I was able to pitch in.

Glad I was able to pitch in.

Larry without your contribution, it would have been unable to solve it in such a short time. Obviously kudos goes to Daniel (Black) for implicating in our thread Monty. I guess it was not a coincidence that we had the best engineers of Maria (and My if you check Sergei's CV :) ) to be involved with this and at such speed. Great stuff. If Sergei succeeds to insert our logic re ORs in his compiler patch we will be getting similar queries to run at over 2500 times faster.

The UNION solution ought to be done "transparently" because it will be silly to try to convert all our code to UNION like statements. Mind you in some cases, when we use temporary tables, we need to create as many temp tables as the selects in the UNION so for small queries the overhead introduced (in creating and dropping the temp tables) is rather substantial. I have faith that he will manage to do it internally.

woocommerce / woocommerce