Index WP MySQL For Speed

Опис

How do I use this plugin?

After you install and activate this plugin, visit the Index MySQL Tool under the Tools menu. From there you can press the Add Keys Now button. If you have large tables, use it with WP-CLI instead to avoid timeouts. See the WP-CLI section to learn more.

What does it do for my site?

This plugin works to make your MySQL database work more efficiently by adding high-performance keys to the tables you choose. On request it monitors your site’s use of your MySQL database to detect which database operations are slowest. It is most useful for large sites: sites with many users, posts, pages, and / or products.

You can use it to restore WordPress’s default keys if need be.

What is this all about?

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. (Many hosting providers and servers use the MariaDB fork of the MySQL software; it works exactly the same way 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. And, bulk imports can take absurd amounts of time. 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. You can, if your hosting provider supports it, install and use a Persistent Object Cache plugin to reduce traffic to your database. That is not the task of this plugin either.

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:

  1. Keep all that data organized.
  2. 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 every one of your posts looking for matches to the author you want. Our users know what that looks like: slow. With the key, MySQL can jump right to the matching posts.

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. Experience with large sites shows that many MySQL slowdowns can be improved by better keys. Code is poetry, data is treasure, and database keys are grease that makes code and data work together smoothly.

Which tables does the plugin add keys to?

This plugin adds and updates keys in these WordPress tables.

  • wp_comments
  • wp_commentmeta
  • wp_posts
  • wp_postmeta
  • wp_termmeta
  • wp_users
  • wp_usermeta
  • wp_options

You only need run this plugin once to get its benefits.

How can I monitor my database’s operation?

On the Index MySQL page (from your Tools menu on your dashboard), you will find the “Monitor Database Operations” tab. Use it to request monitoring for a number of minutes you choose.

You can monitor

  • either the site (your user-visible pages) or the dashboard, or both.
  • all pageviews, or a random sample. (Random samples are useful on very busy sites to reduce monitoring overhead.)

Once you have gathered monitoring information, you can view the captured queries, and sort them by how long they take. Or you can save the monitor information to a file and show it to somebody who knows about database operations. Or you can upload the monitor to the plugin’s servers so the authors can look at it.

It’s a good idea to monitor for a five-minute interval at a time of day when your site is busy. Once you’ve completed a monitor, you can examine it to determine which database operations are slowing you down the most.

Please consider uploading your saved monitors to the plugin’s servers. It’s how we learn from your experience to keep improving. Push the Upload button on the monitor’s tab.

WP-CLI command line operation

This plugin supports WP-CLI. When your tables are large this is the best way to add the high-performance keys: it doesn’t time out.

Give the command wp help index-mysql for details. A few examples:

  • wp index-mysql status shows the current status of high-performance keys.
  • wp index-mysql enable --all adds the high-performance keys to all tables that don’t have them.
  • wp index-mysql enable wp_postmeta adds the high-performance keys to the postmeta table.
  • wp index-mysql disable --all removes the high-performance keys from all tables that have them, restore WordPress’s default keys.
  • wp index-mysql enable --all --dryrun writes out the SQL statements necessary to add the high-performance keys to all tables, but does not run them.
  • wp index-mysql enable --all --dryrun | wp db query writes out the SQL statements and pipes them to wp db to run them.

Note: avoid saving the –dryrun output statements to run later. The plugin generates them to match the current state of your tables.

What’s new in the latest version?

Since the first release, our users have told us about several more opportunities to speed up their WooCommerce and core WordPress operations. We’ve added keys to the meta tables to help with searching for content, and to the users table to look people up by their display names. And, you can now upload saved Monitors so we can see your slowest queries. We’ll use that information to improve future versions. Thanks, dear users!

The plugin now handles WordPress version updates correctly: they don’t change your high-performance keys.

We have added the –dryrun switch to the WP-CLI interface for those who want to see the SQL statements we use.

Credits

  • Michael Uno for Admin Page Framework.
  • Marco Cesarato for LiteSQLParser.
  • Allan Jardine for Datatables.net.
  • Japreet Sethi for advice, and for testing on his large installation.
  • Rick James for everything.

Скріншоти

  • Use Tools > Index MySQL to view the Dashboard panel.
  • Choose tables and add High-Performance Keys.
  • Start Monitoring Database Operations, and see saved monitors.
  • View a saved monitor to see slow database queries.
  • About the plugin.
  • Use WP CLI to run the plugin's operations.

Встановлення

You may install this plugin by visiting Plugins > Add New on your site’s Dashboard, then searching for Index WP MySQL For Speed and following the usual installation workflow.

When you activate it, it will copy a php source file into the must-use plugins directory, wp-content/mu-plugins. Some sites’ configurations prevent the web server from writing files into that directory. In that case the plugin will still work correctly. But, after WordPress core version upgrades you may have to revisit the Tools > Index MySQL page and correct the keying on some tables. Why? The mu-plugin prevents core version updates from trying to change keys.

Composer

If you configure your WordPress installation using composer, you may install this plugin into your WordPress top level configuration with the command

composer require "wpackagist-plugin/index-wp-mysql-for-speed":"^1.4"

During composer installation the plugin can automatically copy the necessary source file (see the previous section) into the must-use plugins directory. If you want that to happen, you should include these scripts in your top-level composer.json file.

 "scripts": {
         "install-wp-mysql-mu-module": [
                 "@composer --working-dir=wordpress/wp-content/plugins/index-wp-mysql-for-speed install-mu-module"
         ],
         "post-install-cmd": [
                 "@install-wp-mysql-mu-module"
         ],
         "post-update-cmd": [
                 "@install-wp-mysql-mu-module"
         ]
     },

Часті питання

Should I back up my site before using this?

Yes. You already knew that.

I don’t see any changes to my database speed. Why not?

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

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.

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

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.

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

What happens to my tables and keys during a WordPress version update?

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

My site has thousands of registered users. My Users, Posts, and Pages panels in my dashboard are still load slowly even with this plugin.

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.

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

How do I get an answer to another question?

Please see more questions and answers here.

Відгуки

27.11.2022 2 replies
I installed a WP plugin named "Index WP MySQL For Speed" to help speed up my website and the directions were please backup your site, (I always back up my website when making any change) and it wouldn't allow me to continue until I backed up my website.. I am a software engineer so I know what I am doing. So I backed up my website. My hosting service Cloudways keep 3 weeks of backups, So I didn't notice a performance increase, in fact it was slower, so I went to restore and it has been looping for an hour, and I got all sorts of server errors. This just happened so my website is still down, and I have contacted tech support to see if they can help restore my website. If they can't restore my website you will be hearing from me
04.11.2022 1 reply
This is a fantastic plugin. I have a wp_postmeta table with more than 700,000 rows. One of the complicated SQL commands consumes me around 2minutes to complete. As a result, the website will show 504 gateway timeout during that 2 mins. The CPU spiked 195% in load. After installing this plugin, it just needs below 5 seconds to complete that query. No 504 gateway timeout anymore. I have spent more than a week to solve the 504 timeout issue. Finally, this plugin solved my problem. Highly recommend.
02.11.2022
This is absolutely fantastic work. I've reviewed your discussions on WP issue tracker and various other places and came to the same conclusion. While I had already added some of my own custom indexes long ago, this is even better. Amazing job!
12.10.2022 4 replies
Let me say two things straight away - firstly its clear from the other very positive reviews of this plugin that my experience is in the minority, for most people the plugin works exceptionally well - the problem is you never know what your experience is going to be until the plugin is installed and then, if you're like me, all hell breaks loose and simply deactivating it makes no difference because the damage by then - for me at least it seemed - had already been done; and secondly I am giving this plugin one star, only because zero stars is not available. An expert A2Hhosting (who host our site) Support Lead installed this plugin (and its sister WP For Speed Users plugin) and both continually crashed the site. Emails to A2 Support went unanswered, the A2Support Chat with A2Hosting call centres made the situation far worse - I spent hours being continually apologised to ("Sorry for the delay", "Thank you for your Patience", "We are working on the Problem" it was like the character Evelyn Green's (Dame Judi Dench) experience in The Best Exotic Marigold Hotel, but nothing was resolved) and having spent thousands of dollars with A2Hosting I felt totally abandoned. Stay clear of these plugins unless you have root access, understand exactly how they work, what they do and that you have guaranteed 'stay with me' support on hand to fix things when they go wrong. Lamentably. I had none of these things.
05.10.2022 1 reply
This is the best WP Plugin ever created. It does what is says and that is to index/speed up large databases. Rick James is great, I have learned a ton from his SO posts and he knows what he is doing. Thanks guys.
Прочитати всі 41 відгук

Учасники та розробники

“Index WP MySQL For Speed” — проект з відкритим вихідним кодом. В розвиток плагіну внесли свій вклад наступні учасники:

Учасники

Перекладіть “Index WP MySQL For Speed” на вашу мову.

Цікавитесь розробкою?

Перегляньте код, перегляньте сховище SVN або підпишіться на журнал розробки за допомогою RSS.

Журнал змін

1.4.7

(no changes to keys)
Prevent WordPress version upgrades from altering high-performance keys.
Add the --dry-run option to WP-CLI, with the same meaning as –dryrun.

1.4.9

(no changes to keys)
Fix defect #45 in the version upgrade logic that prevents altering high-performance keys during version updates. (Don’t run the filter except during version updates, and only on an allowlist of tables.)

1.4.10

(no changes to keys)
Update fix to defect #45.

1.4.11

(no changes to keys)
So long, Heroku, and thanks for all the fish! (New metadata upload site).
Composer support.