Speed Up WooCommerce Site By Adding INDEXes
Problem: WooCommerce Checkout page’s processing speed is REALLY slow (45-120 seconds to complete a transaction –> from after clicking Place Order button on Checkout page until /order-received/ Thank You page loads). After researching, the biggest culprit is the MySQL wp_postmeta table. Which has ~1 million rows.
Solution: Add Keys (INDEXes) to particular tables like the bloated and over-burdened wp_postmeta table.
Troubleshooting also needs to include testing/logging the performance impact of other WordPress plugins that interact with WooCommerce as part of the overall product ordering experience: The Events Calendar, Event Tickets, and Event Tickets Plus.
Test Authorize.net connection: check the direct connect between the host’s servers and the payment gateway’s servers (in this case, Authorize.net’s servers). More details at WooCommerce Speed Test Authorize.net API).
WARNING: Always make a backup of the database BEFORE making any alterations. The below was applied to a Testing site, NOT a Live site.
Environment:
- Testing site has SSL (this is a MUST otherwise Authorize.net Sandbox Test APIs won’t work)
- WooCommerce
- Authorize.net plugin by SkyVerge (which is set to Test Environment and uses the Test API Login ID and Test API Transaction Key from an Authorize.net Sandbox account
- Test purchases made using Authorize.net’s test credit card numbers.
Part 1: “Index WP MySQL For Speed” plugin
Index WP MySQL For Speed plugin adds database keys (also called indexes) to your MySQL tables to make it easier for WordPress to find the information it needs. Plugin also shows which tables need upgrading from deprecated MyISAM to modern InnoDB format, as well as has the ability to add DYNAMIC to those rows:
“These database tables need upgrading to MySQL’s latest table storage format, InnoDB with dynamic rows.”
MySQL uses database keys. Each table has one or more keys. For example, wp_posts has a key to let it quickly find posts when you know the author. Without its post_author key, MySQL would have to scan every one of your posts looking for matches to the author you want. (With WooCommerce, this quickly gets out of hand and the front-end and Admin of the site slow way down).
Which tables does the plugin add keys to?
This plugin adds and updates keys in these WordPress and WooCommerce tables.
- wp_comments
- wp_commentmeta
- wp_posts
- wp_postmeta
- wp_termmeta
- wp_users
- wp_usermeta
- wp_options
- wp_wc_orders_meta
- wp_woocommerce_order_itemmeta
- wp_automatewoo_log_meta
You only need run this plugin once to get its benefits.
“Index WP MySQL For Speed” > High-Performance Keys tab: I chose the “high-performance keys” options when offered; however, I did NOT utilize the “Revert to WordPress’s default keys” option.
Preliminary visual speed results after applying “Index WP MySQL For Speed” plugin
Front-end and Admin of WordPress WooCommerce site are MUCH faster than before using the Index WP MySQL For Speed plugin (including the front-end Events page and the Admin’s Events tab – which were both very slow). More testing is required to see what functionality, if any, has broken.
Part 2: Database Optimization and Cleanup
Database optimization plugins like WP-Optimize or Advanced Database Cleaner for automatic cleanups
Part 3: Test Page Speed
- Set Monitor for 5-minute monitoring in “Index WP MySQL For Speed” > Monitor Database Operations tab (this monitors 5 minutes of MySQL use in real-time).
- Spend the next 5 minutes making a test purchase through the WooCommerce Checkout page, then
- Use “Query Monitor” at the END of the test purchase Checkout process to see if there are any Database Queries > Slow Queries, then
- Click on other pages of the site (to give the “Index WP MySQL For Speed” Monitor enough MySQL usage data).
- Then go back to the “Index WP MySQL For Speed” > Monitor tab and click to see MySQL usage results.
Plugins used:
Resources:
WordPress Database Optimization: Adding Indexes to Custom Tables – SpinupWP: including SQL EXPLAIN statements as well as database optimization plugins like WP-Optimize or Advanced Database Cleaner for automatic cleanups
Does wordpress optimize its database tables by indexing automatically? – Stack Overflow
