Index WP MySQL For Speed

विवरण

What’s new in version 1.4?

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!

How do I use this plugin?

After you install this plugin, use it with the Index MySQL Tool under the Tools menu. If you have large tables, use it with WP-CLI instead to avoid timeouts. Give the shell command wp help index-mysql to learn how.

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.

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.

सामान्य प्रश्न

Should I back up my site before using this?

Yes. You already knew that.

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.

How do I get an answer to another question?

Please see more questions and answers here.

समीक्षाएं

जनवरी 26, 2022
This plugin maybe does some DB optimizations, but I haven't noticed any speed improvements. It is incompatible with some other plugins (like contextual related posts) and with WP 5.9. It killed my site several times. The only plus is that is actively maintained.
जनवरी 24, 2022
As a full time WordPress plugin developer (for 10+ years), it's rare that I find a plugin I recommend, and actively use myself. This is one of them. It's not just another "optimization" plugin, it actually addresses the issues at the database level, which 99.9% of these "optimization" plugins do not. You can select which tables you want to optimize, and even restore the tables back if you're having issues or don't want to use it anymore. Not only that, you have a monitor feature that will monitor the site and report back with results from a defined period of time. Simply amazing, great job guys!
जनवरी 22, 2022
this is one of those "work of art" plugins, you install it, you apply the fixes (after backups, of course) then you see how a clogged 1GB _postmeta table starts to really perform as fast as it has just 100mb or less.
दिसम्बर 24, 2021
If it just works, you may ask, how do I know the developers are responsive? I contacted them because I was reluctant to switch from AriaDB to InnoDB, and a brief exchange told me they really knew their stuff - and I moved over - and it just worked! Very nice, quick performance enhancement. (I had been wondering how WordPress muddled through its apparently insane database model, now I know.)
16 के सभी समीक्षा पढ़ें

सहायक &डेवलपर्स

यह खुला स्रोत सॉफ्टवेयर है। अनुगामी लोगो ने इस प्लगइन के लिए योगदान दिया है।

योगदानकर्ता

बदलाव विवरण

1.3.3

When upgrading tables, change ROW_FORMAT to DYNAMIC as well as ENGINE to InnoDB. Add monitors.

1.3.4

Support MariaDB 10.1, make keys work a little better, miscellaneous bugfixes.

1.4.1

  • WordPress 5.9 and database version 51917 version compatibility tested.
  • Rekeys tables in one go: allows the plugin to work more quickly, and when sql_require_primary_key=ON (typically at managed service providers).
  • Adds high-performance keys to wp_users and wp_commentmeta tables.
  • Adds high-performance keys for filtering on meta_value data quickly in wp_postmeta, wp_termmeta, and wp_usermeta.
  • Handles updates to high-performance keys from previous plugin versions.
  • Checks $wp_db_version number to ensure schema compatibility.
  • Monitor captures include overall database server metrics. Monitor captures can be uploaded.
  • Help pages for each tab of the plugin’s Dashboard panel are available.
  • Clearer Dashboard panel displays.

1.4.2

  • (No changes to indexes.)
  • Add support for legacy php versions back to 5.6.
  • Avoid attempting to read INNODB_METRICS when user lacks PROCESS privilege.
  • Correct nag hyperlink on multisite.