Index Autoload

Description

Adds an index to the autoload in wp_options table and verifies it exists on a daily basis (using WP Cron), resulting in a more efficient database.

Our related OSS projects:

The Long Version

This issue has been debated for several years, and the WP Core team has opted to not include an index in wp_options table for the autoload options for various reasons. Still, many teams have found the index to result in much better database performance, especially for large databases.

VERSION 1.0.3 UPDATE:

As of version 1.0.3, this plugin now works with MyISAM (not only InnoDB) and no longer performs MySQL version check (previously only fired if version was 5.5 or higher). The point is that we assume by installing this plugin you know what you are doing, so only install if you have backed up your database and realize that certain MyISAM database have seen issues when dealing with indexes, etc. This plugin will be most effective for large tables using about 50% (or less) of autoloaded options, both for MyISAM or INNODB engines. For small options tables (most of them) there are no significant differences and this plugin does not have to be installed, although in the vast majority of cases it shouldn’t hurt anything.

Default behavior (after version 1.0.3) is that an index is added upon plugin activation. Then, using WP Cron this plugin will check 1x daily that the index still exists, if not it will be added. The idea here is that rather than worrying about a client or team member messing up your database, you can install this plugin to ensure the index is always there. You can even hide this plugin from a client (etc) by using as a Must Use plugin instead, or include as part of your automated WP installations to avoid having to run manual SQL queries on your project.

Note: uninstalling this plugin will remove the index from your autoload options.

Lastly, if you want to force the index to DROP and then ADD again each day, enable this constant (keep in mind that no other query is performed, such as OPTIMIZE or etc, it is currently just DROP and then ADD again).

define('INDEX_AUTOLOAD_REGENERATE', true);

Please note that the original defined constant no longer works, which allowed you to defined how often the index would be regenerated. This is because 1x daily seems more than enough, and not too often to cause issues. However if you have feedback on these changes, please post on the wp.org forums and tag this plugin in your post.

Compatibility

This plugin has been designed for use on LEMP (Nginx) web servers with PHP 7.0 and MySQL 5.7 to achieve best performance. All of our plugins are meant for single site WordPress installations only; for both performance and security reasons, we highly recommend against using WordPress Multisite for the vast majority of projects.

Plugin Features

  • Settings Page: No
  • Premium Version Available: Yes (Speed Demon)
  • Includes Media (Images, Icons, Etc): No
  • Includes CSS: No
  • Database Storage: Yes
    • Transients: No
    • Options: Yes
    • Creates New Tables: No
  • Database Queries: Backend Only (Options API) + SQL Lookup Cron
  • Must-Use Support: Yes (Use With Autoloader)
  • Multisite Support: No
  • Uninstalls Data: Yes

WP Admin Notices

This plugin generates multiple Admin Notices in the WP Admin dashboard. The first is a notice that fires during plugin activation which recommends several related free plugins that we believe will enhance this plugin’s features; this notice will re-appear approximately once every 6 months as our code and recommendations evolve. The second is a notice that fires a few days after plugin activation which asks for a 5-star rating of this plugin on its WordPress.org profile page. This notice will re-appear approximately once every 9 months. These notices can be dismissed by clicking the (x) symbol in the upper right of the notice box. These notices may annoy or confuse certain users, but are appreciated by the majority of our userbase, who understand that these notices support our free contributions to the WordPress community while providing valuable (free) recommendations for optimizing their website.

If you feel that these notices are too annoying, than we encourage you to consider one or more of our upcoming premium plugins that combine several free plugin features into a single control panel, or even consider developing your own plugins for WordPress, if supporting free plugin authors is too frustrating for you. A final alternative would be to place the defined constant mentioned below inside of your wp-config.php file to manually hide this plugin’s nag notices:

define('DISABLE_NAG_NOTICES', true);

Note: This defined constant will only affect the notices mentioned above, and will not affect any other notices generated by this plugin or other plugins, such as one-time notices that communicate with admin-level users.

Code Inspiration

This plugin was partially inspired either in “code or concept” by the open-source software and discussions mentioned below:

Recommended Plugins

We invite you to check out a few other related free plugins that our team has also produced that you may find especially useful:

Premium Plugins

We invite you to check out a few premium plugins that our team has also produced that you may find especially useful:

Special Thanks

We thank the following groups for their generous contributions to the WordPress community which have particularly benefited us in developing our own free plugins and paid services:

Disclaimer

We released this plugin in response to our managed hosting clients asking for better access to their server, and our primary goal will remain supporting that purpose. Although we are 100% open to fielding requests from the WordPress community, we kindly ask that you keep the above mentioned goals in mind, thanks!

Installation

  1. Upload to /wp-content/plugins/index-autoload-littlebizzy directory
  2. Activate via WP Admin > Plugins
  3. Review your database to confirm index exists

FAQ

Installation Instructions
  1. Upload to /wp-content/plugins/index-autoload-littlebizzy directory
  2. Activate via WP Admin > Plugins
  3. Review your database to confirm index exists

Reviews

It Does it’s job!

I’m in love with this useful tool. Everything works perfect and brings results to the owner that will be left satisfied. Kudos to the author!

Read all 5 reviews

Contributors & Developers

“Index Autoload” is open source software. The following people have contributed to this plugin.

Contributors

Translate “Index Autoload” into your language.

Interested in development?

Browse the code, check out the SVN repository, or subscribe to the development log by RSS.

Changelog

1.1.1

  • updated plugin meta

1.1.0

  • versioning correction (major changes in 1.0.6)
  • (no code changes)
  • (prior major changes should have also been version bumped, but will retain to avoid too much confusion)

1.0.6

  • constant spelling is now define('INDEX_AUTOLOAD_REGENERATE', true);
  • (old spelling no longer supported: IDXALD_REGENERATE)
  • added warning to Multisite installations
  • updated recommended plugins
  • update plugin meta

1.0.5

  • updated recommended plugins

1.0.4

  • tested with WP 4.9
  • added recommended plugins notice
  • added rating request notice
  • added support for define('DISABLE_NAG_NOTICES', true);

1.0.3

  • (once) delete old option index_autoload_active
  • (once) remove WP Cron scheduled hook index_autoload_cron
  • added supported for define('IDXALD_REGENERATE', true);
  • no longer uses IDXAUTOLOAD_SCHEDULE constant
  • fix bug calling wp_unschedule_event
  • fix bug using constant IDXAUTOLOAD_SCHEDULE without checking if defined or not
  • fix compatibility with WPSEO plugin (Yoast) avoiding a fatal error
  • fix DROP query (the last query failed cause DROP queries does not have the ” (autoload)” part, just need the index name
  • no longer checks for MySQL > 5.5 or InnoDB, works with all engine types
  • should now work better (uninstall properly) with PHP 7.1

1.0.2

  • index is now dropped before being (re)added as recommended by Percona and to avoid error log buildup

1.0.1

  • added support for defined constant IDXAUTOLOAD_SCHEDULE
  • check MySQL > 5.5 and InnoDB before run

1.0.0

  • initial release