FAQ

Frequently asked questions and answers about the Index WP MySQL For Speed plugin for WordPress.

I got a “fatal error” when trying to add keys to a table. What can I do?

Please read this.

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?

Please read this.

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 site uses WooCommerce HPOS (High Performance Order Storage). Is this plugin still helpful?

Yes. WooCommerce still uses core WordPress tables for your shop’s products, posts, pages, and users. This plugin adds high-performance keys to those tables.

High Performance Order Storage, true to its name, stores your shop’s orders in a more efficient way. Formerly orders were stored in those same core WordPress tables.

My WordPress host offers MariaDB, not MySQL. Can I use this plugin?

Yes.

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?

Yes. It works correctly with HyperDB and LudicrousDB.

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.

The 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?

My question isn’t answered here!

For support, please use our support forum on WordPress.org. If you may have found a software defect in the plugin, please open an issue on its Github repository.

If you have a general question that’s not answered here, please leave a comment. We do our best to respond.

10 thoughts on “FAQ”

  1. hey Ollie, can you clarify what is meant by “A recent WordPress version update may have changed your high-performance keys. Use the Index WP MySQL For Speed plugin to update your high-performance keys if necessary.”

    If i follow the link supplied to “update the keys” am I done? Its not quite clear how to go about updating keys. There’s a reversion option, but no clear UI for “updating”.

    Reply

    • Thanks for the question. This issue reveals a bit of a software hairball in WordPress’s core upgrade process.

      That process can change tables’ keys. They provide a way for a plugin (like mine) to intercept that process, but that way is hard to use and error prone. (It’s an mu_plugin).

      So, when the plugin detects a recent core version update, it suggests you look at its dashboard page to see if anything needs doing.

      In your case, it seems that it didn’t detect anything needing doing. That is good.

      Reply

  2. Ollie,

    Thank you for identifying bottlenecks in the WP database and improving the keys

    If “add keys now” returns a 504 Gateway Timeout (as it did on our database) will the keys still be added in the background? Or does that indicate they were not and we should instead use wp-cli?

    Reply

  3. Hey Ollie,

    Thank you for making this wonderful plugin. A few things: The description says “You only need to run this plugin once to get its benefits.”. So, can I run this plugin just once, add high-performance keys using this plugin and then later deactivate and delete the plugin? Is that fine? ? There is no need to keep it installed and activated this plugin to get its benefits. Right?

    Also, suppose I used this plugin, changed the key and then deleted the plugin. Now, after many months, if for any reason now I want to remove all high-performance keys from my database. Can I again install and activate this plugin and then disable those keys to restore WordPress’s default keys? Will that work and is good practice?

    One more thing, I ran this plugin and noticed approx. 20MB of size increased in wp_postmeta table. Not any ( or very minute) size change in other tables, though. Does it mean that plugin worked (and be helpful to my website as my DB was indeed big and hence suitable for the use of this plugin ) or it’s bad or it doesn’t really mean anything?

    Thank you.

    Reply

    • It’s hard for me to tell how this plugin interacts with WP Scalability Pro, because that one is not open source. If you use a MariaDB / MySQL client like phpmyadmin, and give the SQL statement

      SHOW CREATE TABLE wp_postmeta;

      and post the output here or send it to me, I might be able to guess.

      I have to say, I wish the authors of the various performance plugins would be more transparent about how they work. That’s the point of open source.

      Reply

  5. Hey, thanks for making this plugin it is quite a tool to debug/improve DB matters. We are having some issues installing the plugin into WPEngine. It is installed and activated, but on the query tab it says “Extended query information, such as the component and affected rows is not available. Query Monitor was unable to symlink its db.php file into place.” and links to db.php Symlink with further instructions. Is there any obvious solutions into that? We checked the server and it has made the symlink so, perhaps permissions are the issue. Which permissions it should have?

    Reply

    • This is an error message from Query Monitor. That plugin, by John Blackbourn, works with a so-called “drop-in” module to intercept database operations. Your message says that Query Monitor couldn’t install the drop-in. I use my plugin together with Query Monitor all the time; my testing matrix calls for making sure it works with and without Query Monitor.

      WPEngine is a managed service, and a very good one. It’s possible WPEngine’s configuration prevents drop-ins from being installed by default. (I think there are some malicious plugins in the wild that use those drop-ins.)

      I worked for a place once that used WPEngine, and I found their tech support people to be super helpful. I think if you call or chat and say “trouble with Query Monitor” they’ll be able to help you very quickly. I could guess what’s wrong and how to fix it, but they will know.

      Reply

Leave a Comment