Speed Up WooCommerce via SQL Query Logging

Problem: WooCommerce site’s Checkout page transaction process is slow (Place Order –> Order Received Thanks You page). And the site’s pages and Admin are slow.

To sell event tickets, the site uses the following WordPress plugins:

• WooCommerce
• The Events Calendar
• Event Tickets
• Event Tickets Plus
• Authorize.net payment gateway

Index WP MySQL For Speed WordPress plugin:

Use Index WP MySQL For Speed plugin to convert the database’s old MySQL table formats from old MyISAM to modern InnoDB. This alone helps speed up the site’s front and Admin pages, but converting the table format does NOT make the Checkout page’s transaction process any faster. Much more needs to be done.

While the Index WP MySQL For Speed plugin does an excellent job of adding Composite Indexes to MySQL (which is a separate procedure from converting MyISAM to InnoDB, as shown above), it only adds Composite Indexes related to WordPress and WooCommerce (source):

• 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

Index WP MySQL For Speed plugin also lists the High Performance Keys (composite indexes) that they add to WordPress and WooCommerce table. Here’s their table-by-table rundown of the WordPress and WooCommerce tables they affect and their keys.

But since we also have Event Tickets, Event Tickets Plus, and The Events Calendar plugins to optimize, we need to go beyond the Index WP MySQL For Speed plugin.

For instance, Composite Indexes need to be added to increase WooCommerce and Events Calendar performance. And this is something that the Index WP MySQL For Speed plugin does not index.

Going Beyond Index WP MySQL For Speed plugin:

Using ChatGPT to build an SQL query logging script, checkout-profiler.php, so that SQL queries are only logged during the Place Order → Thank You page cycle. The script generates 2 logs:

• checkout-profiler.log (located at: /wp-content/checkout-profiler.log)
• checkout-profiler-sql.log (located at: /wp-content/checkout-profiler-sql.log)

ChatGPT’s analysis of the checkout-profiler-sql.log reveals:

• Heavy wp_postmeta Usage

and especially:

SELECT wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
WHERE wp_postmeta.meta_key = '_tribe_wooticket_for_event'
  AND wp_postmeta.meta_value IN ('28450')
GROUP BY wp_posts.ID
ORDER BY wp_posts.menu_order ASC

which indicate repeated full scans or inefficient lookups on wp_postmeta. This is a known WooCommerce and Events Calendar performance bottleneck.

Check for the following indexes in phpMyAdmin:

CREATE INDEX idx_meta_key_value ON wp_postmeta(meta_key(191), meta_value(191));
CREATE INDEX idx_post_id_meta_key ON wp_postmeta(post_id, meta_key(191));

In phpMyAdmin, verify with:

SHOW INDEX FROM wp_postmeta;

–> If those indexes are missing, add Composite Indexes to wp_postmeta MySQL tables to speed up WooCommerce and Events Calendar performance.

ChatGPT has many other solutions as well.