Index WP MySQL For Speed plugin for WordPress.Frequently asked questions and answers about the
- I got a “fatal error” when trying to add keys to a table. What can I do?
- Should I back up my site before using this?
- I don’t see any changes to my database speed. Why not?
- I use a nonstandard database table prefix. Will this work ?
- What tables and keys does the plugin change?
- Is this safe? Can I add high-performance keys and revert back to WordPress standard keys safely?
- My WordPress host offers MariaDB, not MySQL. Can I use this plugin?
- Which versions of MySQL and MariaDB does this support?
- What database Storage Engine does this support?
- Which versions of MySQL and MariaDB work best?
- Is this plugin compatible with WordPress Object Cache plugins for redis, memcached, or SQLite?
- Why did the size of my tables grow when I added high-performance keys?
- Will the new keys be valid for new data in the tables?
- Is this plugin compatible with replicated load-balanced database configurations?
- Does this plugin generate any overhead when my site is busy?
- What happens when I deactivate this plugin?
- Does this work on my multisite (network) WordPress instance?
- After using this plugin, I can’t optimize or repair my database. Is this normal?
- Can I upgrade my WordPress instance to multisite after using this plugin?
- Can I restore a backup or duplicate to another server after using this plugin?
- Does this plugin’s code access any external web services?
- Will this plugin fix misconfigurations in my MySQL or MariaDB server?
- I get a “Temporary file write failure” error. What do I do about this?
- Why did WordPress’s core version update log some SQL errors?
- How can I learn more about this business of database keys?
- My question isn’t answered here!
I got a “fatal error” when trying to add keys to a table. What can I do?
Should I back up my site before using this?
Yes. You already knew that. Seriously, if you’re not sure whether your site is backed up, please don’t use this plugin. The authors are working hard to make it beginner-friendly, but it’s not there yet.
I don’t see any changes to my database speed. Why not?
- On a modestly sized site (with a few users and a few hundred posts) your database may be fast enough without these keys. The speed improvements are most noticeable on larger sites with many posts and products.
- Just installing and activating the plugin is not enough to make it work. Don’t forget to visit the Index MySQL Tool under the Tools menu. From there you can press the Add Keys Now button.
I use a nonstandard database table prefix. Will this work ?
Yes. Some WordPress databases have nonstandard prefixes. That is, their tables are named something_posts, something_postmeta, and so forth instead of wp_posts and wp_postmeta. This works with those databases.
What tables and keys does the plugin change?
Is this safe? Can I add high-performance keys and revert back to WordPress standard keys safely?
Yes. it is safe to add keys and revert them. Changing keys is a routine database-maintenance operation.
My WordPress host offers MariaDB, not MySQL. Can I use this plugin?
Which versions of MySQL and MariaDB does this support?
MySQL versions 5.5.62 and above, 5.6.4 and above, 8 and above. MariaDB version 5.5 and above.
What database Storage Engine does this support?
InnoDB only. If your tables use MyISAM (the older storage engine) or the older COMPACT row format, this plugin offers to upgrade them for you.
Which versions of MySQL and MariaDB work best?
If at all possible upgrade to Version 8 or later of MySQL. For MariaDB upgrade to Version 10.3 or later. The MySQL and MariaDB developers have made many performance improvements over the past few years. They have the mission of making things better for WordPress site operators: we are by far their biggest user base. So, we have a lot to gain by using their latest versions.
Avoid Versions 5.5 of both MySQL and MariaDB if you can. And, avoid MariaDB 10.1. They use the older Antelope version of InnoDB. It has a limitation on index lengths that requires WordPress to use prefix keys. Those have reduced performance.
If your database server uses the later Barracuda version of InnoDB, this plugin uses its capability to build efficient covering keys. If you have the older Antelope version it still builds keys, but they are less efficient. The prefix keys it uses cannot be covering keys.
For more information about WordPress’s prefix keys please read this.
Is this plugin compatible with WordPress Object Cache plugins for redis, memcached, or SQLite?
Yes. This plugin only affects WordPress’s queries to the database.
Persistent object caching can help your site’s database performance by reducing its workload. You can read about it here.
If your hosting provider doesn’t offer redis or memcached cache-server technology you can try using our SQLite Object Cache plugin for the purpose.
If you have trouble with your Object Cache plugin, try flushing (removing all entries from) the Object Cache immediately after you activate this plugin.
Why did the size of my tables grow when I added high-performance keys?
Database keying works by making copies of your table’s data organized in ways that are easy to randomly access. Your MariaDB or MySQL server automatically maintains the copies of your data as you insert or update rows to each table.
And, the keying task adjusts the amount of free space in each block of your table’s data in preparation for the insertion of new rows. When free space is available, inserting new rows doesn’t require relatively slow block splits. Tables that have been in use for a long time often need new free space in many blocks.
When adding keys, it is normal for table sizes to increase. It’s the oldest tradeoff in computer science: time vs. space.
Will the new keys be valid for new data in the tables?
Yes. Once the keys are in place MariaDB and MySQL automatically maintain them as you update, delete, or insert rows of data to your tables. There is no need to do anything to apply the keys to new data: the DBMS software does that for you.
Is this plugin compatible with replicated load-balanced database configurations?
Does this plugin generate any overhead when my site is busy?
No, not unless you are using it to monitor database operations, and that is for limited periods of time.
Some plugins’ code runs whenever your visitors view pages. All this plugin’s rekeying work happens from the WordPress Dashboard or WP-CLI. It sets up the keys in your database and then gets out of the way. You can even deactivate and delete the plugin once you’ve run it.
What happens when I deactivate this plugin?
Its high-performance keys remain in place. You can always re-add it and reactivate the plugin if you need to revert your keys to the WordPress standard.
We remove your saved monitors and settings when you deactivate the plugin. (And, we wish all plugin authors would make their plugins do the same.)
Does this work on my multisite (network) WordPress instance?
Yes. On multisite instances, you must activate the plugin from the Network Admin dashboard. The Index MySQL tool is available for use by the administrator on each site.
After using this plugin, I can’t optimize or repair my database. Is this normal?
Yes, it is normal.
OPTIMIZE and REPAIR are legacy MySQL commands for the legacy MyISAM storage engine. If you used this plugin you probably upgraded your tables from MyISAM to InnoDB. InnoDB is far more robust about maintaining internal integrity than is MyISAM — it’s self-optimizing and self-repairing.
OPTIMIZE TABLE and
REPAIR TABLE SQL statements no longer do anything useful once you have InnoDB tables, and they are no longer needed.
Can I upgrade my WordPress instance to multisite after using this plugin?
No. if you upgrade your WordPress instance to multisite (a network) following these instructions, revert your high-performance keys first. After you complete your upgrade you can add back the high-performance keys.
Can I restore a backup or duplicate to another server after using this plugin?
Yes. But if you restore it to a server with an older version of MySQL (looking at you, GoDaddy) you should revert your keys to the WordPress standard before creating your backup or duplicate.
Does this plugin’s code access any external web services?
Yes. Only when you upload metadata or saved monitors, it performs a POST request to our server. It does not access any other external services.
Will this plugin fix misconfigurations in my MySQL or MariaDB server?
No. This plugin only upgrades your tables to InnoDB if necessary, and updates their keys. It does not handle any other configuration issues.
Database servers have many configuration settings. Occasionally some of them are wrong and the database server software performs poorly. The Percona Toolkit offers a utility called pt-variable-advisor. If you have command-line access to your server you can run it. It will make suggestions for better settings.
I get a “Temporary file write failure” error. What do I do about this?
Your database server machine has a file system partition for temporary files. If that partition is not big enough to hold a copy of the largest of your tables, the rekeying operation won’t work. This is most often the case on Linux or FreeBSD file systems with a separate
/tmp partition. You can ask the person who operates your database server to change the startup option from
tmpdir = /tmp to
tmpdir = /var/tmp to resolve this problem.
Why did WordPress’s core version update log some SQL errors?
(Version 1.4.7 fixes this issue.) When WordPress core does its version update (for example when updating from version 5.8.3 to 5.9) it notices non-standard keys on your tables and makes a half-hearted attempt to restore the standard keys. It ends up restoring some standard keys. Your site will function correctly after the update, but it’s a good idea to use the plugin again to put back the high-performance keys. You’ll be prompted to do that by a message at the top of your dashboard pages when this plugin detects a recent version update.
You can avoid this issue entirely by restoring the standard keys before doing the WordPress version update, then adding the high performance keys again after the update.
How can I learn more about this business of database keys?
It’s a large topic. Some people (often called Database Administrators–DBAs) make entire careers out of this kind of work. Where can you look to get started?
- Marcus Winand’s great book Use The Index, Luke.
- Rick James, a contributor to this plugin, has a good article Building the best INDEX for a given SELECT.
- StackOverflow’s Why are references to wp_postmeta so slow? is useful.
- So is wordpress.stackexchange.com‘s article Simple SQL query on wp_postmeta very slow.
- Good information about the wp_options table from web agency 10up.com. This plugin puts keys on that table to optimize options loading.
- Here is a history of how WordPress’s use of MySQL came to be the way it is.
My question isn’t answered here!
If you have a general question that’s not answered here, please leave a comment. We do our best to respond.