Index WP MySQL For Speed

外掛說明

How do I use this plugin?

Use this plugin with the Index MySQL Tool under the Tools menu. Or, give the shell command wp help index-mysql to learn how to use it with WP-CLI.

What does it do for my site?

This plugin works to make your MySQL database work more efficiently by adding high-performance keys to its tables. It also monitors your site’s use of your MySQL database to detect which database operations are slowest.

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 WordPress; it works exactly the same 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. 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.

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 the entire table looking for posts matching the author you want. We all know what that looks like: slow.

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. Code is poetry, data is treasure, and database keys are grease that makes code and data work together smoothly.

What tables does it add keys to?

This plugin updates keys in six tables found in all WordPress installations.

  • wp_options
  • wp_posts
  • wp_postmeta
  • wp_comments
  • wp_usermeta
  • wp_termmeta

Experience with large sites shows that many MySQL slowdowns can be improved by better keys. 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 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.

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.

螢幕擷圖

常見問題集

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

Is this plugin compatible with WordPress Object Cache plugins for redis and memcached?

Yes. This plugin only affects WordPress’s queries to the database. The Object Cache plugins reduce the number of those queries, and so reduce your database’s workload.

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

Your saved monitors are removed when you deactivate the plugin.

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.

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.

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.

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?

使用者評論

2021 年 11 月 3 日
I am using this plugin for some time now and all I can say is that I am very happy with this plugin! I have 85.000 products and my search results in the admin has changed for some queries from like 6 seconds to around a second.
2021 年 10 月 18 日
Great plugin that fixed our database speed problems. Not to mention amazing support. Thanks Ollie!
2021 年 10 月 9 日
my wp-admin was very slow. Your plugin helped improve browsing speed and reduce CPU consumption when I edit posts.
2021 年 9 月 24 日
Works great for my woocommerce websites as well as for my other websites. Worth all 5 stars!
閱讀全部 11 則使用者評論

參與者及開發者

以下人員參與了開源軟體〈Index WP MySQL For Speed〉的開發相關工作。

參與者

將 Index WP MySQL For Speed 外掛本地化為台灣繁體中文版

對開發相關資訊感興趣?

任何人均可瀏覽程式碼、查看 SVN 存放庫,或透過 RSS 訂閱開發記錄

變更記錄

0.9.1

First release.

1.0.1

Works for multisite, add more user choices

1.0.2

Do not upgrade the storage engine for views or for non-WordPress tables.

1.2.0

Add WP-CLI support. Add selective storage-enging upgrades. Add the Reset option to put back WordPress standard keys on tables with unrecognized combinations of keys.

1.2.1

Fix require_once defect exposed by wp-cli workflow.

1.2.2

Fix engine-upgrade defect, stop counting rows because it’s too slow.

1.2.3

Fix cli defect.

1.3.3

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