Is your WordPress or WooCommerce site too slow? Use this plugin to speed it up by adding high-performance keys (database indexes) to your MySQL database tables. It’s free open source code released under the GNU Public License.
How do I use this plugin?
Install it like you would install any WordPress plugin. Download it from the plugin repository on WordPress.org or install it using the Plugins > Add New panel on your WordPress dashboard.
Use it with the Index MySQL Tool under the Tools menu. Or, give the shell command wp help index-mysql to learn how to use it with WP-CLI.
What does it do for my site?
This plugin works to make your MySQL database work more efficiently by adding high-performance keys to its tables. It also monitors your site’s use of your MySQL database to detect which database operations are slowest. It is most useful for large sites: sites with many users, posts, pages, and / or products.
What is this all about?
Where does WordPress store all that stuff that makes your site great? Where are your pages, posts, WooCommerce products, media, users, customers, custom fields, metadata, and all your valuable content? All that data is in the MySQL relational database management system. (Many hosting providers and servers use the MariaDB fork of the MySQL software; it works exactly the same as MySQL itself.)
As your site grows, your MySQL tables grow. Giant tables can make your page loads slow down, frustrate your users, and even hurt your search-engine rankings. What can you do about this?
You can install and use a database cleaner plugin to get rid of old unwanted data and reorganize your tables. That makes them smaller, and therefore faster. That is a good and necessary task. That is not the task of this plugin.
You can, if your hosting provider supports it, install and use a Persistent Object Cache plugin to reduce traffic to your database. If you run a large site you should do that. That is not the task of this plugin either.
How does this plugin work?
This plugin adds database keys (also called indexes) to your MySQL tables to make it easier for WordPress to find the information it needs. MySQL and all relational database management systems store your information in long-lived tables. For example, WordPress stores your posts, products, and other content in a table called wp_posts, and custom post fields in another table called wp_postmeta. A successful site can have thousands of posts and hundreds of thousands of custom post fields. MySQL has two jobs:
- 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. Your users know what that looks like: slow. With the key, MySQL can jump right to the matching posts.
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.
Do I need it?
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 like MySQL and MariaDB 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 database system 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.)
Which tables does the plugin add keys to?
This plugin adds and updates keys in these WordPress tables.
- wp_comments
- wp_commentmeta
- wp_options
- wp_posts
- wp_postmeta
- wp_termmeta
- wp_users
- wp_usermeta
- wp_woocommerce_order_itemmeta
- wp_wc_orders_meta
- wp_automatewoo_log_meta
You only need run this plugin once to get its benefits. We recommend you leave it activated, however: WordPress.org core updates attempt to undo these key changes. When this plugin is activated it prevents those attempts.
How can I monitor my database’s operation?
If you’re not sure whether you need to add and update keys to your tables, you can use this plugin to monitor you database operations for a period of time and analyze which database operations — which queries — are slowest. (You can also use John Blackbourn’s Query Monitor plugin.)
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 queries, and sort them by how long they take. You can save the monitor information to a file and show it to somebody who knows about database operations, and you can upload it to this plugin’s servers if you want to contact the authors about 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.
Credits
- Michael Uno for Admin Page Framework.
- Marco Cesarato for LiteSQLParser.
- Allan Jardine for Datatables.net.
- Japreet Sethi for advice, and for testing on his large installation.
- Leho Kraav and Sebastian Sommer for encouraging the handling of WooCommerce tables.
- Rick James for everything.
I have more questions
Please see the FAQ here.