Bulk Editing of a Few Products Incredibly Slow

Hello @reberiii,

Thank you for reaching out.

I understand your concern about the slow speed of bulk editing in your WooCommerce store, even after upgrading to a more powerful AWS instance.

Although it’s true that having a large number of products and variations can impact the speed, it’s unusual for the delay to be this significant. The memory allocation you’ve done seems more than adequate, so it’s unlikely that this is a memory issue.

Here are a few things you can try to troubleshoot this issue even further:

1. Check for Plugin Conflicts: Sometimes, third-party plugins can interfere with WooCommerce’s operations. I suggest temporarily deactivating all non-WooCommerce plugins, then trying the bulk edit operation again. If the speed improves, reactivate the plugins one by one until you find the one causing the slowdown.
2. Database Optimization: Over time, databases can accumulate unnecessary data, slowing down operations. Use a plugin like this one to clean and optimize your database.
3. Ensure you’re running the latest version of WooCommerce. We regularly release updates to improve performance and fix bugs.

Please try these suggestions and let us know how it goes.

We’re here to assist you further if needed.

Thank you for your quick response!

1. I disabled all of the plugins and ran a test with only WooCommerce running. This did not have an effect on speed.
2. I ran WP-Optimize database optimization items and that did decrease the run time of the operation from about 80 seconds to 54 (I had a stopwatch).
3. We’re running: PHP 8.2, WordPress 6.7 and WooCommerce 9.3. As far as I know, all plugins on the site (17) are fully up to date.

Again, this is only three products that are being set to “Out of Stock”, so even 54 seconds, although an improvement, still seems painfully slow.

Other information — this site is about 6 years old. I know there was a storage performance update with WooCommerce in the not too distant past, so I don’t know if that would help here or not. I’m not currently getting any errors of consequence in my Plesk logs, aside from the new one: “PHP message: Automattic\WooCommerce\Admin\Features\Navigation\Menu::add_plugin_item is deprecated since 9.3”, but I hardly think that has anything to do with this issue.

Am I in need of a more powerful database with AWS? Are there more database optimizations or updates that need to be performed? Thank you for the help!

Hello! I wanted to follow up quickly — I did create a new database instance with AWS that doubled the RAM from 1 to 2GB, and that had no effect on the speed of this process. It still took 65 seconds to take three products “In Stock”, and then 58 seconds to take the same three products “Out of Stock”. I have since reverted to the 1GB database instance.

Any ideas are appreciated!

Hey @reberiii ,

Thanks for trying out those troubleshooting steps! It looks like you’ve covered a lot of ground already, so let’s explore a few more possibilities. Since the upgraded database didn’t speed things up, it might help to take a closer look at how your database handles product data. Using a plugin like Query Monitor can reveal what’s happening with database queries during bulk edits. Sometimes a specific query can be the bottleneck, and adding or adjusting indexing could help it run more smoothly.

WooCommerce also relies on transients to cache data, which can sometimes build up and slow things down. Can you head over to WooCommerce → Status → Tools and clear the WooCommerce transients and Expired transients if you haven’t done this recently. A quick cleanup here could give your bulk actions a bit of a speed boost.

Lastly, it may be helpful to check the browser console while you’re doing a bulk edit. Occasionally, AJAX or other script issues can be the source of unexpected delays, and errors may show up in the console that aren’t always visible elsewhere.

If none of these steps do the trick, please can you share a copy of your System Status Report here. You can find this via WooCommerce?→?Status. Select?Get system report?and then?Copy for support.

And if Query Monitor points out any slow queries, or you notice any console errors, please share those too!

Cheers,

Hello @beautyofcode! Thanks for the help.

I’ve actually had Query Monitor installed on this site for a few years now, and unfortunately the plugin doesn’t report anything useful in this instance as the page will allow the scripts to run for the 55-65 seconds, and then when completed, Query Monitor shows that the page load took approximately 1.5 to 2 seconds (I wish!). There is the logging feature, but I a) don’t really know where to/how to set the appropriate breakpoints and b) I’m not totally sure it would be able to show what’s going on here.

Great idea about the transients — took care of that the way you recommended and also using WP-Optimize. Those are clean now, but there was no discernible speed boost (maybe super tiny?).

Finally, with the developer console, it seems that the page is throwing some JS errors which don’t seem to consequential, but then edit.php is throwing some errors relating to cookies and “the non-standard property ‘zoom'”. Nothing stands out to me, but I’m entirely sure what to look for there.

I’ll post the WooCommerce System Status Report next. However, a couple bits of new information — our team has reported that the site is also extremely slow (and sometimes times out with nginx 504 errors) when duplicating existing products (with several variations, sometimes over 50) or when publishing new products with multiple variations as well.

This site’s SQL export file is close to 300MB in size. Additionally, each of those products is very likely to 20 to 70 variations each. It just seems like there needs to be a way to optimize the interaction between the edit.php page and the many lines in the posts table of the DB. Here’s the status:

WordPress Environment<br><br>WordPress address (URL): https://spiritwear.fipcreative.com<br>Site address (URL): https://spiritwear.fipcreative.com<br>WC Version: 9.4.1<br>Legacy REST API Package Version: The Legacy REST API plugin is not installed on this site.<br>Action Scheduler Version: ? 3.8.2<br>Log Directory Writable: ?<br>WP Version: 6.7<br>WP Multisite: –<br>WP Memory Limit: 2 GB<br>WP Debug Mode: –<br>WP Cron: ?<br>Language: en_US<br>External object cache: ? Server Environment<br><br>Server Info: nginx/1.26.2<br>PHP Version: 8.2.25<br>PHP Post Max Size: 8 MB<br>PHP Time Limit: 90<br>PHP Max Input Vars: 3000<br>cURL Version: 7.68.0<br>OpenSSL/1.1.1f<br><br>SUHOSIN Installed: –<br>MySQL Version: 8.0.35<br>Max Upload Size: 2 MB<br>Default Timezone is UTC: ?<br>fsockopen/cURL: ?<br>SoapClient: ?<br>DOMDocument: ?<br>GZip: ?<br>Multibyte String: ?<br>Remote Post: ?<br>Remote Get: ? Database<br><br>WC Database Version: 9.4.1<br>WC Database Prefix: wp_<br>Total Database Size: 426.97MB<br>Database Data Size: 288.83MB<br>Database Index Size: 138.14MB<br>wp_woocommerce_sessions: Data: 0.47MB + Index: 0.02MB + Engine MyISAM<br>wp_woocommerce_api_keys: Data: 0.00MB + Index: 0.00MB + Engine MyISAM<br>wp_woocommerce_attribute_taxonomies: Data: 0.00MB + Index: 0.00MB + Engine MyISAM<br>wp_woocommerce_downloadable_product_permissions: Data: 0.00MB + Index: 0.00MB + Engine MyISAM<br>wp_woocommerce_order_items: Data: 1.29MB + Index: 0.70MB + Engine MyISAM<br>wp_woocommerce_order_itemmeta: Data: 17.45MB + Index: 9.00MB + Engine MyISAM<br>wp_woocommerce_tax_rates: Data: 0.00MB + Index: 0.01MB + Engine MyISAM<br>wp_woocommerce_tax_rate_locations: Data: 0.00MB + Index: 0.00MB + Engine MyISAM<br>wp_woocommerce_shipping_zones: Data: 0.00MB + Index: 0.00MB + Engine MyISAM<br>wp_woocommerce_shipping_zone_locations: Data: 0.00MB + Index: 0.00MB + Engine MyISAM<br>wp_woocommerce_shipping_zone_methods: Data: 0.00MB + Index: 0.00MB + Engine MyISAM<br>wp_woocommerce_payment_tokens: Data: 0.00MB + Index: 0.00MB + Engine MyISAM<br>wp_woocommerce_payment_tokenmeta: Data: 0.00MB + Index: 0.00MB + Engine MyISAM<br>wp_woocommerce_log: Data: 0.00MB + Index: 0.00MB + Engine MyISAM<br>wp_actionscheduler_actions: Data: 6.52MB + Index: 5.00MB + Engine InnoDB<br>wp_actionscheduler_claims: Data: 0.02MB + Index: 0.02MB + Engine InnoDB<br>wp_actionscheduler_groups: Data: 0.02MB + Index: 0.02MB + Engine InnoDB<br>wp_actionscheduler_logs: Data: 4.52MB + Index: 3.03MB + Engine InnoDB<br>wp_commentmeta: Data: 0.00MB + Index: 0.00MB + Engine MyISAM<br>wp_comments: Data: 5.67MB + Index: 2.17MB + Engine MyISAM<br>wp_imagify_files: Data: 0.00MB + Index: 0.00MB + Engine MyISAM<br>wp_imagify_folders: Data: 0.00MB + Index: 0.00MB + Engine MyISAM<br>wp_iwp_backup_status: Data: 0.05MB + Index: 0.00MB + Engine InnoDB<br>wp_iwp_file_list: Data: 0.02MB + Index: 0.02MB + Engine InnoDB<br>wp_iwp_processed_iterator: Data: 0.02MB + Index: 0.00MB + Engine InnoDB<br>wp_links: Data: 0.00MB + Index: 0.00MB + Engine MyISAM<br>wp_mail_catcher_logs: Data: 29.52MB + Index: 0.00MB + Engine InnoDB<br>wp_options: Data: 2.16MB + Index: 0.27MB + Engine MyISAM<br>wp_postmeta: Data: 155.69MB + Index: 64.95MB + Engine MyISAM<br>wp_posts: Data: 22.18MB + Index: 5.82MB + Engine MyISAM<br>wp_term_relationships: Data: 1.54MB + Index: 2.73MB + Engine MyISAM<br>wp_term_taxonomy: Data: 0.10MB + Index: 0.04MB + Engine MyISAM<br>wp_termmeta: Data: 0.05MB + Index: 0.05MB + Engine MyISAM<br>wp_terms: Data: 0.04MB + Index: 0.07MB + Engine MyISAM<br>wp_tm_taskmeta: Data: 1.52MB + Index: 0.22MB + Engine InnoDB<br>wp_tm_tasks: Data: 1.52MB + Index: 0.08MB + Engine InnoDB<br>wp_usermeta: Data: 0.01MB + Index: 0.01MB + Engine MyISAM<br>wp_users: Data: 0.00MB + Index: 0.01MB + Engine MyISAM<br>wp_wc_admin_note_actions: Data: 0.06MB + Index: 0.02MB + Engine InnoDB<br>wp_wc_admin_notes: Data: 0.06MB + Index: 0.00MB + Engine InnoDB<br>wp_wc_category_lookup: Data: 0.02MB + Index: 0.00MB + Engine InnoDB<br>wp_wc_customer_lookup: Data: 0.17MB + Index: 0.13MB + Engine InnoDB<br>wp_wc_download_log: Data: 0.00MB + Index: 0.00MB + Engine MyISAM<br>wp_wc_order_addresses: Data: 2.52MB + Index: 2.86MB + Engine InnoDB<br>wp_wc_order_coupon_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB<br>wp_wc_order_operational_data: Data: 2.52MB + Index: 0.61MB + Engine InnoDB<br>wp_wc_order_product_lookup: Data: 1.52MB + Index: 0.56MB + Engine InnoDB<br>wp_wc_order_stats: Data: 0.28MB + Index: 0.22MB + Engine InnoDB<br>wp_wc_order_tax_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB<br>wp_wc_orders: Data: 4.52MB + Index: 2.11MB + Engine InnoDB<br>wp_wc_orders_meta: Data: 17.55MB + Index: 23.14MB + Engine InnoDB<br>wp_wc_product_attributes_lookup: Data: 1.52MB + Index: 1.52MB + Engine InnoDB<br>wp_wc_product_download_directories: Data: 0.02MB + Index: 0.02MB + Engine InnoDB<br>wp_wc_product_meta_lookup: Data: 7.52MB + Index: 12.61MB + Engine InnoDB<br>wp_wc_rate_limits: Data: 0.02MB + Index: 0.02MB + Engine InnoDB<br>wp_wc_reserved_stock: Data: 0.02MB + Index: 0.00MB + Engine InnoDB<br>wp_wc_tax_rate_classes: Data: 0.02MB + Index: 0.02MB + Engine InnoDB<br>wp_wc_webhooks: Data: 0.00MB + Index: 0.00MB + Engine MyISAM<br>wp_wcpdf_invoice_number: Data: 0.00MB + Index: 0.00MB + Engine MyISAM<br>wp_wml_entries: Data: 0.03MB + Index: 0.00MB + Engine InnoDB<br>wp_wpmailsmtp_debug_events: Data: 0.02MB + Index: 0.00MB + Engine InnoDB<br>wp_wpmailsmtp_tasks_meta: Data: 0.02MB + Index: 0.00MB + Engine InnoDB Post Type Counts<br><br>af_c_fields: 1<br>attachment: 14478<br>def_reg_fields: 10<br>nav_menu_item: 3<br>page: 8<br>post: 1<br>product: 2107<br>product_variation: 54928<br>shop_coupon: 30<br>shop_order: 9657<br>shop_order_placehold: 11<br>shop_order_refund: 228<br>wccf_product_field: 14<br>wcpa_pt_forms: 11<br>wp_global_styles: 2 Security<br><br>Secure connection (HTTPS): ?<br>Hide errors from visitors: ? Active Plugins (18)<br><br>Disable Bloat for WordPress & WooCommerce: by Disable Bloat – 3.4.9<br>Gallery Captions for WooCommerce: by John Beales – 1.9.1<br>Export Order Items Pro for WooCommerce: by WP Zone – 2.1.33<br>Query Monitor: by John Blackbourn – 3.16.4<br>Redis Object Cache: by Till Krüss – 2.5.4<br>Server IP & Memory Usage Display: by Apasionados<br>Apasionados del Marketing – 2.1.0<br><br>Woocommerce Custom Product Addons (5.x.x): by Acowebs – 5.1.0<br>WooCommerce.com Update Manager: by Automattic – 1.0.3<br>WooCommerce Checkout Field Editor: by WooCommerce – 1.7.18<br>WooCommerce Stripe Gateway: by Stripe – 8.8.2<br>PDF Invoices & Packing Slips for WooCommerce: by WP Overnight – 3.9.0<br>WooCommerce Protected Categories: by Barn2 Plugins – 2.7.5<br>WooCommerce: by Automattic – 9.4.1<br>ManageWP - Worker: by GoDaddy – 4.9.20<br>Mail logging - WP Mail Catcher: by James Ward – 2.1.10<br>WP Mail SMTP: by WP Mail SMTP – 4.2.0<br>WP Migrate Lite: by WP Engine – 2.6.11<br>WP-Optimize - Clean, Compress, Cache: by TeamUpdraft<br>DavidAnderson – 3.7.0 Inactive Plugins (3)<br><br>Imagify: by Imagify Image Optimizer – Optimize Images & Convert WebP & Avif – 2.2.3.1<br>WooCommerce Custom Fields: by RightPress – 2.3.4<br>WP Sheet Editor - WooCommerce Products: by WP Sheet Editor – 1.8.15 Dropin Plugins ()<br><br>advanced-cache.php: advanced-cache.php<br>db.php: Query Monitor Database Class (Drop-in)<br>object-cache.php: Redis Object Cache Drop-In Must Use Plugins (3)<br><br>Endurance Page Cache: by Mike Hansen – 2.2.1<br>ManageWP - Worker Loader: by GoDaddy – 1.0.0<br>WP Migrate Lite Compatibility: by Delicious Brains – 1.3 Settings<br><br>Legacy API Enabled: –<br>Force SSL: ?<br>Currency: USD ($)<br>Currency Position: left<br>Thousand Separator: ,<br>Decimal Separator: .<br>Number of Decimals: 2<br>Taxonomies: Product Types: external (external)<br>grouped (grouped)<br>simple (simple)<br>variable (variable)<br><br>Taxonomies: Product Visibility: exclude-from-catalog (exclude-from-catalog)<br>exclude-from-search (exclude-from-search)<br>featured (featured)<br>outofstock (outofstock)<br>rated-1 (rated-1)<br>rated-2 (rated-2)<br>rated-3 (rated-3)<br>rated-4 (rated-4)<br>rated-5 (rated-5)<br><br>Connected to WooCommerce.com: ?<br>Enforce Approved Product Download Directories: –<br>HPOS feature enabled: ?<br>Order datastore: Automattic\WooCommerce\Internal\DataStores\Orders\OrdersTableDataStore<br>HPOS data sync enabled: – Logging<br><br>Enabled: ?<br>Handler: Automattic\WooCommerce\Internal\Admin\Logging\LogHandlerFileV2<br>Retention period: 30 days<br>Level threshold: –<br>Log directory size: 4 MB WC Pages<br><br>Shop base: #4 - /shop/<br>Cart: #5 - /cart/ - Contains the [woocommerce_cart] shortcode<br>Checkout: #6 - /checkout/ - Contains the [woocommerce_checkout] shortcode<br>My account: #7 - /my-account/<br>Terms and conditions: #1411 - /terms-and-conditions/ Theme<br><br>Name: Storefront Child<br>Version: 1.0<br>Author URL: https://woocommerce.com<br>Child Theme: ?<br>Parent Theme Name: Storefront<br>Parent Theme Version: 4.6.0<br>Parent Theme Author URL: https://woocommerce.com/<br>WooCommerce Support: ? Templates<br><br>Overrides: – Admin<br><br>Enabled Features: activity-panels<br>analytics<br>product-block-editor<br>coupons<br>core-profiler<br>customize-store<br>customer-effort-score-tracks<br>import-products-task<br>experimental-fashion-sample-products<br>shipping-smart-defaults<br>shipping-setting-tour<br>homescreen<br>marketing<br>mobile-app-banner<br>navigation<br>onboarding<br>onboarding-tasks<br>pattern-toolkit-full-composability<br>product-custom-fields<br>remote-inbox-notifications<br>remote-free-extensions<br>payment-gateway-suggestions<br>printful<br>shipping-label-banner<br>subscriptions<br>store-alerts<br>transient-notices<br>woo-mobile-welcome<br>wc-pay-promotion<br>wc-pay-welcome-page<br>launch-your-store<br><br>Disabled Features: product-data-views<br>experimental-blocks<br>minified-js<br>product-pre-publish-modal<br>settings<br>async-product-editor-category-field<br>product-editor-template-system<br>blueprint<br>reactify-classic-payments-settings<br>use-wp-horizon<br><br>Daily Cron: ? Next scheduled: 2024-11-15 17:53:10 -05:00<br>Options: ?<br>Notes: 105<br>Onboarding: completed Action Scheduler<br><br>Complete: 18,134<br>Oldest: 2024-10-15 01:57:14 +0000<br>Newest: 2024-11-14 17:40:43 +0000<br><br>Failed: 54<br>Oldest: 2022-04-05 17:48:37 +0000<br>Newest: 2024-11-12 20:25:33 +0000<br><br>Pending: 4<br>Oldest: 2024-11-15 13:23:24 +0000<br>Newest: 2024-12-13 16:19:37 +0000 Status report information<br><br>Generated at: 2024-11-14 14:53:34 -05:00<br>

Hello reberiii,

Thank you for your reply.

I understand your site is experiencing slow responses, particularly when duplicating or publishing products with 20 to 70 variations, and it sometimes results in Nginx 504 errors.

This could be related to the large number of variations.
Could you export a few products into a CSV file and share them with me?
I’d like to review the setup and test the products on my test site to investigate further.

I recommend contacting your host to check for usage spikes and identify potential causes.

Lastly, could you specify which AWS service are you using?
Like is it EC2, Lightsail, or some other?

Looking forward to your response. ??

Hello @doublezed2!

I think you might be on to something.

Our original complaint was that the speed of bulk edits on the product listing page was painfully slow — to bulk edit three products from “In Stock” to “Out of Stock” would take 55-65 seconds before the process would finish, and would sometimes result in a 504. We also have noticed the duplicating and publishing slowness as well.

When exporting the products for you to see in a CSV file, I selected 5 at random to export. This process of exporting took 35(!) minutes to complete. The resulting file is 10.4MB in size! It’s 55K lines! For comparison sake, I exported 5 products from a completely different WooCommerce install, and the operation took a matter of seconds and was only 167KB in size, which seems much more normal to me.

Here is a link to the file: https://file.io/9k98gzZh3zYT

For posterity sake, the server is a AWS Lightsail instance with 4 vCPU, 16GB of RAM and 320GB of storage.

What can I do about this? I can understand processes being slow if each product in the store averages 2MB in a CSV file!

Your help is very much appreciated.

OK, that’s my mistake, and I can’t seem to edit my previous post. My CSV that I posted is of ALL of the products — I had checked the boxes to export five products, but then I hit the Export button at the top of the page which doesn’t care about my checked boxes and it exported all 2000 items. Embarrassing.

So, maybe not onto anything just yet as to why our operations are taking so long. Anyone’s assistance on this is appreciated.

Hi @reberiii

Thanks for the update on that. I think it would be best if you could remove the file from file.io.

Editing products on admin is a database intensive process, and bulk editing does not reduce the size of the query, but actually increases the amount of processing which takes place.

You did ask if HPOS (High Performance Order Storage) would help and the answer to this is yes, if you have a high number of orders, it allows you to move meta data from wp_postmeta to the new custom order tables and remove the legacy order data from the wp_postmeta and wp_posts table. This reduces the number of rows in the table, where many heavy JOIN queries are run.

The impact will be higher as the number of orders increases. If you currently have no orders, the impact will not be significant at this point.

Performance of the admin dashboard can be improved by using an external object caching mechanism, such as Redis. In fact, I would say an external object cache is essential.

the server is a AWS Lightsail instance with 4 vCPU, 16GB of RAM and 320GB of storage.

Scaling vertically, so that your database server is separated from your web server, is recommended as your needs scale. This allows the DB server to be built optimally for the task, while the webserver can have a different configuration. Our experience at this point is that autoscaling solutions, rather than static individual server instances, might be better suited for larger stores.

Alternative methods of managing stock, which implement either through the REST API or through a plugin which is designed for bulk stock management (search WooCommerce bulk stock management) may be more efficient.

A pro tip here: running a CSV export, removing all columns but SKU and stock numbers from the CSV, editing the stock and then reimporting can be a quick and dirty way to manage bulk stock updates.

Could you please give us a copy of your System Status? You can find it via WooCommerce > Status. Select “Get system report” and then “Copy for support”. That will give us some idea of what we’re dealing with.

Apologies – I see you have already added a system status report. The DB size is not huge, and you are already running HPOS, so that question is also answered.

I would try an external object cache next.

Also ensure that your autoload query is not too large: a large autoload query (that is all options which are marked as ‘autoload=yes’) can swamp your SQL Query cache. Autoload should be no more than 500kb.

• This reply was modified 5 days, 23 hours ago by nicw.a11n.

Hello @nicw!

Thank you for the very thorough and thoughtful responses. Here’s what I have for updates:

• HPOS does appear to be enabled for the store, although, I only did that recently and it didn’t seem to make much if any of a performance boost. We currently have 2000+ products and 9000+ orders in the store’s history. Is it possible that something simply didn’t work correctly when enabling HPOS? Postmeta is 221MB in size whilst the Orders_meta table is 40MB
• Redis Object Cache is running successfully on the server and it honestly does make a performance boost in many tasks — just not when we go to bulk edit a handful of products or when we duplicate and publish new products
• Our database server is already a separate instance, so that makes me feel good about our decisions there. I did (listed earlier in the thread) attempt to upgrade the Lightsail RDS from a 1GB RAM to a 2GB RAM instance without any performance increase
• A plugin to perform bulk edits could certainly be useful here, but it does not seem that it would help with duplicating products, publishing products with a large number of variations, or with creating new variations on a new product (which can lead to a script timeout and a 504 — I did already increase that time from 60 to 90 seconds)
• I am not well versed in autoload, but I did run the following query on the database (taken from an online doc):
  • SELECT ‘autoloaded data in KiB’ as name, ROUND(SUM(LENGTH(option_value))/ 1024) as value FROM wp_options WHERE autoload=’yes’ UNION SELECT ‘autoloaded data count’, count(*) FROM wp_options WHERE autoload=’yes’;
  • And it aggregated 116KB, so that doesn’t seem too bad? The total count was 455
  • The top autoload option, in length, is one that is a random string of characters and a length of 21K. Next is rewrite_rules at 18K

So, I’m still at a loss here for what to do to improve the performance of these operations. The server is powerful enough, the RDS is adequate, and the configurations we’ve looked at seem to be solid. All help is appreciated!

Here’s are some additional updates:

• When moving one single product from In Stock to Out of Stock via the Quick Edit menu, this operation takes over 20+ seconds. This product was a duplicated product, but all of the variations were deleted and replaced with fresh variations.
• In clicking around the backend and trying to duplicate products to create new ones to test, I have noticed some very slow queries from Query Monitor. Most are around 0.5 seconds when slow, but this one is north of 7 seconds. It’s from WordPress Core, and is the SELECT DISTINCT meta_key FROM wp_postmeta

Your help is appreciated!

Another update:

• Used a method described here to attack the slow query: https://wordpress.stackexchange.com/questions/187612/admin-very-slow-edit-page-caused-by-core-meta-query The function seems to have helped with that slow query, but the process described above of a duplicated product taking 20+ seconds still exists.

Hi there,

Looking over your System Status Report, I see that while you have HPOS enabled, and Custom Tables are authoritative, you are still syncing legacy order data. I would recommend turning this off, and then using the tool in WooCommerce > Status > Tools to remove legacy data from the wp_posts and wp_postmeta table.

You can re-enable sync at any time.

I’m not sure where you currently stand on SQL indexing, but a tool such as a SQL indexing plugin can be handy.

Could we try removing that additional data, and then if you could let us have another System Status Report, it would show us any changes to your database.

Hello @nicw !

Went into Advanced > Features, and it looks like the box for “Enable compatibility mode” is already unchecked and says that 9,973 orders are not synced. Makes me wonder if something didn’t go right there when HPOS was enabled before.

Additionally, went into Status > Tools, and it looks like “Clean up order data from legacy tables” has nothing that can be done (the button is greyed out and says “No orders in need of cleanup”. Again, I wonder if this is accurate or not.

Next, I installed the “Index WP MySQL for Speed” plugin to attempt the SQL indexing that you recommended. Went through the process of converting the keys/index for each table in the database aside from wp_wc_orders_meta, and honestly — I feel like things might have gotten a touch slower, at least at the start. I used that plugin to start a monitoring session in which I went and took the same three products in and out of stock, and the operation still takes 55-65 seconds, and unfortunately, the monitoring didn’t show anything that stood out as the potential issue.

Finally, I tested publishing products with variations and without. A product with 50 variations take 22 seconds to “Update” after changing the price of one of the variants. A product with 0 variations still takes 19 seconds to update after only changing a character of the description.

I’ll post the system status next and hopefully that helps. Today I’ll be removing the first five years of order data from the system after archiving it to spreadsheets and hopefully that gives a speed boost somehow.

WordPress Environment<br><br>WordPress address (URL): https://spiritwear.fipcreative.com<br>Site address (URL): https://spiritwear.fipcreative.com<br>WC Version: 9.4.1<br>Legacy REST API Package Version: The Legacy REST API plugin is not installed on this site.<br>Action Scheduler Version: ? 3.8.2<br>Log Directory Writable: ?<br>WP Version: 6.7<br>WP Multisite: –<br>WP Memory Limit: 2 GB<br>WP Debug Mode: –<br>WP Cron: ?<br>Language: en_US<br>External object cache: ? Server Environment<br><br>Server Info: nginx/1.26.2<br>PHP Version: 8.2.25<br>PHP Post Max Size: 8 MB<br>PHP Time Limit: 90<br>PHP Max Input Vars: 3000<br>cURL Version: 7.68.0<br>OpenSSL/1.1.1f<br><br>SUHOSIN Installed: –<br>MySQL Version: 8.0.35<br>Max Upload Size: 2 MB<br>Default Timezone is UTC: ?<br>fsockopen/cURL: ?<br>SoapClient: ?<br>DOMDocument: ?<br>GZip: ?<br>Multibyte String: ?<br>Remote Post: ?<br>Remote Get: ? Database<br><br>WC Database Version: 9.4.1<br>WC Database Prefix: wp_<br>Total Database Size: 821.92MB<br>Database Data Size: 451.98MB<br>Database Index Size: 369.94MB<br>wp_woocommerce_sessions: Data: 0.30MB + Index: 0.02MB + Engine InnoDB<br>wp_woocommerce_api_keys: Data: 0.02MB + Index: 0.03MB + Engine InnoDB<br>wp_woocommerce_attribute_taxonomies: Data: 0.02MB + Index: 0.02MB + Engine InnoDB<br>wp_woocommerce_downloadable_product_permissions: Data: 0.02MB + Index: 0.06MB + Engine InnoDB<br>wp_woocommerce_order_items: Data: 2.52MB + Index: 1.52MB + Engine InnoDB<br>wp_woocommerce_order_itemmeta: Data: 42.64MB + Index: 50.77MB + Engine InnoDB<br>wp_woocommerce_tax_rates: Data: 0.02MB + Index: 0.06MB + Engine InnoDB<br>wp_woocommerce_tax_rate_locations: Data: 0.02MB + Index: 0.03MB + Engine InnoDB<br>wp_woocommerce_shipping_zones: Data: 0.02MB + Index: 0.00MB + Engine InnoDB<br>wp_woocommerce_shipping_zone_locations: Data: 0.02MB + Index: 0.05MB + Engine InnoDB<br>wp_woocommerce_shipping_zone_methods: Data: 0.02MB + Index: 0.00MB + Engine InnoDB<br>wp_woocommerce_payment_tokens: Data: 0.02MB + Index: 0.02MB + Engine InnoDB<br>wp_woocommerce_payment_tokenmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB<br>wp_woocommerce_log: Data: 0.02MB + Index: 0.02MB + Engine InnoDB<br>wp_actionscheduler_actions: Data: 7.03MB + Index: 8.94MB + Engine InnoDB<br>wp_actionscheduler_claims: Data: 0.02MB + Index: 0.02MB + Engine InnoDB<br>wp_actionscheduler_groups: Data: 0.02MB + Index: 0.02MB + Engine InnoDB<br>wp_actionscheduler_logs: Data: 6.06MB + Index: 3.50MB + Engine InnoDB<br>wp_commentmeta: Data: 0.02MB + Index: 0.05MB + Engine InnoDB<br>wp_comments: Data: 8.52MB + Index: 12.61MB + Engine InnoDB<br>wp_imagify_files: Data: 0.02MB + Index: 0.08MB + Engine InnoDB<br>wp_imagify_folders: Data: 0.02MB + Index: 0.03MB + Engine InnoDB<br>wp_iwp_backup_status: Data: 0.05MB + Index: 0.00MB + Engine InnoDB<br>wp_iwp_file_list: Data: 0.02MB + Index: 0.02MB + Engine InnoDB<br>wp_iwp_processed_iterator: Data: 0.02MB + Index: 0.00MB + Engine InnoDB<br>wp_links: Data: 0.02MB + Index: 0.02MB + Engine InnoDB<br>wp_mail_catcher_logs: Data: 33.02MB + Index: 0.00MB + Engine InnoDB<br>wp_options: Data: 3.52MB + Index: 0.52MB + Engine InnoDB<br>wp_postmeta: Data: 261.00MB + Index: 223.34MB + Engine InnoDB<br>wp_posts: Data: 38.58MB + Index: 19.06MB + Engine InnoDB<br>wp_term_relationships: Data: 7.52MB + Index: 4.52MB + Engine InnoDB<br>wp_term_taxonomy: Data: 0.23MB + Index: 0.16MB + Engine InnoDB<br>wp_termmeta: Data: 0.09MB + Index: 0.22MB + Engine InnoDB<br>wp_terms: Data: 0.09MB + Index: 0.09MB + Engine InnoDB<br>wp_tm_taskmeta: Data: 1.52MB + Index: 0.28MB + Engine InnoDB<br>wp_tm_tasks: Data: 1.52MB + Index: 0.11MB + Engine InnoDB<br>wp_usermeta: Data: 0.02MB + Index: 0.05MB + Engine InnoDB<br>wp_users: Data: 0.02MB + Index: 0.06MB + Engine InnoDB<br>wp_vi_wbe_history: Data: 0.02MB + Index: 0.00MB + Engine InnoDB<br>wp_wc_admin_note_actions: Data: 0.06MB + Index: 0.02MB + Engine InnoDB<br>wp_wc_admin_notes: Data: 0.06MB + Index: 0.00MB + Engine InnoDB<br>wp_wc_category_lookup: Data: 0.02MB + Index: 0.00MB + Engine InnoDB<br>wp_wc_customer_lookup: Data: 0.17MB + Index: 0.13MB + Engine InnoDB<br>wp_wc_download_log: Data: 0.02MB + Index: 0.03MB + Engine InnoDB<br>wp_wc_order_addresses: Data: 2.52MB + Index: 2.86MB + Engine InnoDB<br>wp_wc_order_coupon_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB<br>wp_wc_order_operational_data: Data: 2.52MB + Index: 0.61MB + Engine InnoDB<br>wp_wc_order_product_lookup: Data: 1.52MB + Index: 0.56MB + Engine InnoDB<br>wp_wc_order_stats: Data: 0.28MB + Index: 0.22MB + Engine InnoDB<br>wp_wc_order_tax_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB<br>wp_wc_orders: Data: 4.52MB + Index: 2.11MB + Engine InnoDB<br>wp_wc_orders_meta: Data: 17.55MB + Index: 23.14MB + Engine InnoDB<br>wp_wc_product_attributes_lookup: Data: 0.34MB + Index: 0.23MB + Engine InnoDB<br>wp_wc_product_download_directories: Data: 0.02MB + Index: 0.02MB + Engine InnoDB<br>wp_wc_product_meta_lookup: Data: 7.52MB + Index: 13.56MB + Engine InnoDB<br>wp_wc_rate_limits: Data: 0.02MB + Index: 0.02MB + Engine InnoDB<br>wp_wc_reserved_stock: Data: 0.02MB + Index: 0.00MB + Engine InnoDB<br>wp_wc_tax_rate_classes: Data: 0.02MB + Index: 0.02MB + Engine InnoDB<br>wp_wc_webhooks: Data: 0.02MB + Index: 0.02MB + Engine InnoDB<br>wp_wcpdf_invoice_number: Data: 0.02MB + Index: 0.00MB + Engine InnoDB<br>wp_wml_entries: Data: 0.03MB + Index: 0.00MB + Engine InnoDB<br>wp_wpmailsmtp_debug_events: Data: 0.02MB + Index: 0.00MB + Engine InnoDB<br>wp_wpmailsmtp_tasks_meta: Data: 0.02MB + Index: 0.00MB + Engine InnoDB Post Type Counts<br><br>af_c_fields: 1<br>attachment: 14506<br>def_reg_fields: 10<br>nav_menu_item: 3<br>page: 8<br>post: 1<br>product: 2067<br>product_variation: 54021<br>shop_coupon: 30<br>shop_order_placehold: 9973<br>wccf_product_field: 14<br>wcpa_pt_forms: 11<br>wp_global_styles: 2 Security<br><br>Secure connection (HTTPS): ?<br>Hide errors from visitors: ? Active Plugins (20)<br><br>Disable Bloat for WordPress & WooCommerce: by Disable Bloat – 3.4.9<br>Gallery Captions for WooCommerce: by John Beales – 1.9.1<br>Export Order Items Pro for WooCommerce: by WP Zone – 2.1.33<br>Index WP MySQL For Speed: by Oliver Jones<br>Rick James – 1.5.2<br><br>Query Monitor: by John Blackbourn – 3.16.4<br>Redis Object Cache: by Till Krüss – 2.5.4<br>Server IP & Memory Usage Display: by Apasionados<br>Apasionados del Marketing – 2.1.0<br><br>Woocommerce Custom Product Addons (5.x.x): by Acowebs – 5.1.0<br>Advanced Order Export For WooCommerce: by AlgolPlus – 3.5.6<br>WooCommerce.com Update Manager: by Automattic – 1.0.3<br>WooCommerce Checkout Field Editor: by WooCommerce – 1.7.18<br>WooCommerce Stripe Gateway: by Stripe – 8.8.2<br>PDF Invoices & Packing Slips for WooCommerce: by WP Overnight – 3.9.0<br>WooCommerce Protected Categories: by Barn2 Plugins – 2.7.5<br>WooCommerce: by Automattic – 9.4.1<br>ManageWP - Worker: by GoDaddy – 4.9.20<br>Mail logging - WP Mail Catcher: by James Ward – 2.1.10<br>WP Mail SMTP: by WP Mail SMTP – 4.2.0<br>WP Migrate Lite: by WP Engine – 2.6.11<br>WP-Optimize - Clean, Compress, Cache: by TeamUpdraft<br>DavidAnderson – 3.7.0 Inactive Plugins (5)<br><br>Bulky - Bulk Edit Products for WooCommerce: by VillaTheme – 1.2.7<br>Imagify: by Imagify Image Optimizer – Optimize Images & Convert WebP & Avif – 2.2.3.1<br>PW WooCommerce Bulk Edit: by Pimwick<br>LLC – 2.131<br><br>WooCommerce Custom Fields: by RightPress – 2.3.4<br>WP Sheet Editor - WooCommerce Products: by WP Sheet Editor – 1.8.15 Dropin Plugins ()<br><br>advanced-cache.php: advanced-cache.php<br>db.php: Query Monitor Database Class (Drop-in)<br>object-cache.php: Redis Object Cache Drop-In Must Use Plugins (4)<br><br>Endurance Page Cache: by Mike Hansen – 2.2.1<br>Index WP MySQL For Speed Upgrade Filter for mu-plugins.: by – 1.5.2<br>ManageWP - Worker Loader: by GoDaddy – 1.0.0<br>WP Migrate Lite Compatibility: by Delicious Brains – 1.3 Settings<br><br>Legacy API Enabled: –<br>Force SSL: ?<br>Currency: USD ($)<br>Currency Position: left<br>Thousand Separator: ,<br>Decimal Separator: .<br>Number of Decimals: 2<br>Taxonomies: Product Types: external (external)<br>grouped (grouped)<br>simple (simple)<br>variable (variable)<br><br>Taxonomies: Product Visibility: exclude-from-catalog (exclude-from-catalog)<br>exclude-from-search (exclude-from-search)<br>featured (featured)<br>outofstock (outofstock)<br>rated-1 (rated-1)<br>rated-2 (rated-2)<br>rated-3 (rated-3)<br>rated-4 (rated-4)<br>rated-5 (rated-5)<br><br>Connected to WooCommerce.com: ?<br>Enforce Approved Product Download Directories: –<br>HPOS feature enabled: ?<br>Order datastore: Automattic\WooCommerce\Internal\DataStores\Orders\OrdersTableDataStore<br>HPOS data sync enabled: – Logging<br><br>Enabled: ?<br>Handler: Automattic\WooCommerce\Internal\Admin\Logging\LogHandlerFileV2<br>Retention period: 30 days<br>Level threshold: –<br>Log directory size: 4 MB WC Pages<br><br>Shop base: #4 - /shop/<br>Cart: #5 - /cart/ - Contains the [woocommerce_cart] shortcode<br>Checkout: #6 - /checkout/ - Contains the [woocommerce_checkout] shortcode<br>My account: #7 - /my-account/<br>Terms and conditions: #1411 - /terms-and-conditions/ Theme<br><br>Name: Storefront Child<br>Version: 1.0<br>Author URL: https://woocommerce.com<br>Child Theme: ?<br>Parent Theme Name: Storefront<br>Parent Theme Version: 4.6.0<br>Parent Theme Author URL: https://woocommerce.com/<br>WooCommerce Support: ? Templates<br><br>Overrides: – Admin<br><br>Enabled Features: activity-panels<br>analytics<br>product-block-editor<br>coupons<br>core-profiler<br>customize-store<br>customer-effort-score-tracks<br>import-products-task<br>experimental-fashion-sample-products<br>shipping-smart-defaults<br>shipping-setting-tour<br>homescreen<br>marketing<br>mobile-app-banner<br>navigation<br>onboarding<br>onboarding-tasks<br>pattern-toolkit-full-composability<br>product-custom-fields<br>remote-inbox-notifications<br>remote-free-extensions<br>payment-gateway-suggestions<br>printful<br>shipping-label-banner<br>subscriptions<br>store-alerts<br>transient-notices<br>woo-mobile-welcome<br>wc-pay-promotion<br>wc-pay-welcome-page<br>launch-your-store<br><br>Disabled Features: product-data-views<br>experimental-blocks<br>minified-js<br>product-pre-publish-modal<br>settings<br>async-product-editor-category-field<br>product-editor-template-system<br>blueprint<br>reactify-classic-payments-settings<br>use-wp-horizon<br><br>Daily Cron: ? Next scheduled: 2024-11-20 17:53:10 -05:00<br>Options: ?<br>Notes: 106<br>Onboarding: completed Action Scheduler<br><br>Complete: 22,605<br>Oldest: 2024-10-19 20:30:48 +0000<br>Newest: 2024-11-19 17:46:27 +0000<br><br>Failed: 56<br>Oldest: 2022-04-05 17:48:37 +0000<br>Newest: 2024-11-18 21:36:33 +0000<br><br>Pending: 4<br>Oldest: 2024-11-20 13:31:05 +0000<br>Newest: 2024-12-13 16:19:37 +0000 Status report information<br><br>Generated at: 2024-11-19 13:28:23 -05:00<br>