Index WP MySQL For Speed

Mô tả

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.

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

This plugin updates those keys. It works on 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.

Ảnh màn hình

  • The settings page.

  • WP-CLI terminal.

Hỏi đáp

Should I back up my site before using this?

Yes. You already knew that.

It takes a long time to display the plugin’s settings page. Why?

This plugin examines your MySQL database as it renders its settings page. On shared hosts that can take a while. Please be patient, and please avoid clicking the Index MySQL link more than once.

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

Avoid Versions 5.5 of both MySQL and MariaDB if you can. They use the older Antelope version of InnnoDB. 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. It must use prefix keys on that version. Those cannot be covering keys.

Does this plugin generate any overhead when my site is busy?

No. Some plugins’ code runs when your visitors view pages. All this plugin’s 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 it if you need to revert your keys to the WordPress standard.

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.

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?

Đánh giá

10 Tháng Chín, 2021
For a huge Website is important to optimize the DB Performance. Many Thanks! 🙂
24 Tháng Tám, 2021
This plugin is no BS. I had to figure out my wordpress password just to leave a review. After a big woocommerce data import, my site so slow. Something about indexes being off. I ran this plugin, and it's fixed!
6 Tháng Tám, 2021
Work nice and as expected. I would love to have some kind of benchmark to measure the improvement. Thanks
19 Tháng Sáu, 2021
For years, this was one of my tricks to optimize DB speed of large sites. Finally arrived in a form of a plugin. Thanks!
Đọc tất cả 5 đánh giá

Người đóng góp & Lập trình viên

“Index WP MySQL For Speed” là mã nguồn mở. Những người sau đã đóng góp vào plugin này.

Những người đóng góp

Nhật ký thay đổi

0.1.2

First publication.

0.1.4

Minor updates.

0.9.1

More complete diagnostic information upload, minor usability and documentation improvements.

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