Index WP MySQL For Speed for WordPress

Keep all that data organized.

Find the data it needs quickly.

To do its second job, 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. Our users know what that looks like: slow. With the key, MySQL can jump right to the matching posts.

In a new WordPress site with a couple of users and a dozen posts, the keys don’t matter very much. As the site grows the keys start to matter, a lot. Database management systems are designed to have their keys updated, adjusted, and tweaked as their tables grow. They’re designed to allow the keys to evolve without changing the content of the underlying tables. In organizations with large databases adding, dropping, or altering keys doesn’t change the underlying data. It is a routine maintenance task in many data centers. If changing keys caused databases to lose data, the MySQL and MariaDB developers would hear howling not just from you and me, but from many heavyweight users. (You should still back up your WordPress instance of course.)

Better keys allow WordPress’s code to run faster without any code changes. Experience with large sites shows that many MySQL slowdowns can be improved by better keys. Code is poetry, data is treasure, and database keys are grease that makes code and data work together smoothly.

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.

How can I monitor my database’s operation? On the Index MySQL page (from your Tools menu on your dashboard), you will find the “Monitor Database Operations” tab. Use it to request monitoring for a number of minutes you choose.

You can monitor

either the site (your user-visible pages) or the dashboard, or both.

all pageviews, or a random sample. (Random samples are useful on very busy sites to reduce monitoring overhead.)

Once you have gathered monitoring information, you can view the captured queries, and sort them by how long they take. Or you can save the monitor information to a file and show it to somebody who knows about database operations. Or you can upload the monitor to the plugin’s servers so the authors can look at it.

It’s a good idea to monitor for a five-minute interval at a time of day when your site is busy. Once you’ve completed a monitor, you can examine it to determine which database operations are slowing you down the most.

Please consider uploading your saved monitors to the plugin’s servers. It’s how we learn from your experience to keep improving. Push the Upload button on the monitor’s tab.

WP-CLI command line operation This plugin supports WP-CLI. When your tables are large this is the best way to add the high-performance keys: it doesn’t time out.

Give the command wp help index-mysql for details. A few examples:

wp index-mysql status shows the current status of high-performance keys.

wp index-mysql enable --all adds the high-performance keys to all tables that don’t have them.

wp index-mysql enable wp_postmeta adds the high-performance keys to the postmeta table.

wp index-mysql disable --all removes the high-performance keys from all tables that have them, restoring WordPress’s default keys.

wp index-mysql enable --all --dryrun writes out the SQL statements necessary to add the high-performance keys to all tables, but does not run them.

wp index-mysql enable --all --dryrun | wp db query writes out the SQL statements and pipes them to wp db to run them.

Note: avoid saving the –dryrun output statements to run later. The plugin generates them to match the current state of your tables.

Why use this plugin? Three reasons (maybe four):

to save carbon footprint.

to save carbon footprint.

to save carbon footprint.

to save people time.

Seriously, the microwatt hours of electricity saved by faster web site technologies add up fast, especially at WordPress’s global scale.

How can I learn more about making my WordPress site more efficient? We offer several plugins to help with your site’s database efficiency. You can read about them here.

Credits

Michael Uno for Admin Page Framework.

Marco Cesarato for LiteSQLParser.

Allan Jardine for Datatables.net.

Leho Kraav and Sebastian Sommer for suggesting the WooCommerce tables.

Japreet Sethi for advice, and for testing on his large installation.

Rick James for everything.

Jetbrains for their IDE tools, especially PhpStorm. It’s hard to imagine trying to navigate an epic code base without their tools.