Use this plugin 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.
Where does WordPress store all that stuff that makes your site great? Where are your pages, posts, products, media, users, custom fields, metadata, and all your valuable content? All that data is in the MySQL relational database management system. (Some hosting providers and servers use the MariaDB fork of WordPress; 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.
This plugin adds database keys (also called indexes) to your MySQL tables to make it easier for WordPress to find the information it needs. All relational database management systems store your information in long-lived tables. For example, WordPress stores your posts 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 the entire table looking for posts matching the author you want. We all know what that looks like: slow.
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. Code is poetry, data is treasure, and database keys are grease that makes code and data work together smoothly.
This plugin updates those keys. It works on six tables found in all WordPress installations.
- wp_options
- wp_posts
- wp_postmeta
- wp_comments
- wp_usermeta
- wp_termmeta
Experience with large sites shows that many MySQL slowdowns can be improved by better keys. You only need run this plugin once to get its benefits.
0.1.2
First publication.
0.1.4
Minor updates.
0.9.1
More complete diagnostic information upload, minor usability and documentation improvements.
1.0.1
Works for multisite, add more user choices
1.0.2
Do not upgrade the storage engine for views or for non-WordPress tables.
1.2.0
Add WP-CLI support. Add selective storage-enging upgrades. Add the Reset option to put back WordPress standard keys on tables with unrecognized combinations of keys.
1.2.1
Fix require_once defect exposed by wp-cli workflow.
1.2.2
Fix engine-upgrade defect, stop counting rows because it’s too slow..
1.2.3
Fix cli defect.
