Index WP MySQL For Speed

• 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.
• 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.

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.

Yes.

MySQL versions 5.5.62 and above, 5.6.4 and above, 8 and above. MariaDB versions 5.5.62 and above.

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.

Please read this.

Yes. it is safe to add keys and revert them. Changing keys is a routine database-maintenance operation.

As you know you should still keep backups of your site: other things can cause data loss.

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.

This plugin only changes database indexes. If the other plugin does not change database indexes, it is very likely compatible with this one.

Of course, if you find an incompatibility please open a support topic.

Sometimes the Index WP MySQL For Speed plugin for WordPress generates errors when you use it to add keys. These can look like this or similar:

Fatal error: Uncaught ImfsException: [0]: Index for table 'wp_postmeta' is corrupt; try to repair it 

First, don’t panic. This (usually) does not mean your site has been corrupted. It simply means your MariaDB or MySQL server was not able to add the keys to that particular table. Your site will still function, but you won’t get the benefit of high-performance keys on the particular table. Very large tables are usually the ones causing this kind of error. Very likely you ran out of temporary disk space on your MariaDB or MySQL database server machine. The database server makes a temporary copy of a table when you add keys to it; that allows it to add the keys without blocking your users.

It’s possible to correct this problem by changing your MariaDB or MySQL configuration. Instructions are here.

If the plugin is activated during a WordPress version update, it prevents the update workflow from removing your high-performance keys (Version 1.4.7).

Yes. On multisite instances, you must activate the plugin from the Network Admin dashboard. After network activation, the Index MySQL tool is available for use by the administrator on each site.

If you have many subsites, you may prefer to use WP-CLI to add the high-performance keys to each site in turn. Commands like these will work, if you mention each blogid in turn.:

wp index-mysql enable --all --blogid=1  wp index-mysql enable --all --blogid=2  wp index-mysql enable --all --blogid=3 

We have another plugin to handle lots of users, Index WP Users For Speed. Due to the way WordPress handles users, just changing database keys is not enough to solve those performance problems.

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.

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.

Yes. Once the high-performance 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.

You can revert the keys from the Index MySQL Tool under the Tools menu, or use the wp-cli command wp index-mysql disable --all. Notice that if you deactivate or delete the plugin without doing this, the high-performance keys remain.

If you start the name of the index with the characters index_wp_mysql_protect_ this plugin ignores it and leaves it in place. For example if yoo want an index on wp_posts.guid you can create it like this:

ALTER TABLE wp_posts ADD KEY index_wp_mysql_protect_guid ON wp_posts(guid);` 

Notice how index_wp_mysql_protect_guid, the name of the key, starts with those characters.

Please see more questions and answers here.