If you’ve ever had to deal with a medium to large WordPress instance, you’ve also had to deal with an extensive database, many rows, and a sluggish back-end/front-end.
WordPress Database Indexes
You might not know, but a plugin by the famous Oliver Jones and Rick James optimizes some problematic databse tables that either have no indexes or poorly created indexes. You can download and install it on your WordPress site and update the indexes on your tables.
The tables that are modified are the following:
You can read more about why the change is needed in the following article.
The GitHub issue aims to implement the database index changes highlighted in the Index WP MySQL for Speed WordPress plugin by Oliver Jones and Rick James.
The changes would occur using the Performance Lab plugin by the WordPress Performance Team, allowing testing of the changes to eventually be placed into WordPress core.
My 2 Cents
I’ve put my two cents as a reply in the GitHub issue, you might notice the issue has a number of replies and has a lot of back and forth.
This is just a bystander’s point of view. Dealing with medium and large sites with lots of data.
This is an excellent summation of the issue at hand https://www.plumislandmedia.net/index-wp-mysql-for-speed/wordpresss-prefix-keys/. Correct me if I’m wrong.
There are many options to address this issue; the least amount of work required would be the most efficient. One major roadblock is supporting multiple different DBMS versions. Supporting InnoDB Barracuda is ideal, effectively changing the minimum requirements.
Below are the minimum requirements for WordPress, when Barracuda was set as the default for MariaDB/MySQL innodb file formats and the EOL of both MariaDB/MySQL.
- Minimum requirement for WordPress is MariaDB 10.3 or greater.
- MariaDB 10.2.2 and later supports Barracuda as default, and antelope is deprecated.
- MariaDB EOL
- MariaDB 10.3 is EOL in 2 months (25 May 2023)
- MariaDB 10.4 is EOL in 1 year and 3 months. (18 Jun 2024)
- Maria 10.5 is EOL in 2 years and 3 months (24 Jun 2025)
- Maria 10.6 (LTS) is EOL in 3 years (06 Jul 2026)
- Minimum requirement for WordPress is MySQL 5.7 or greater.
- MySQL 5.6 Documentations is wiped from mysql.com *(Google Cached Version)
- MySQL 5.7 and later supports Barracuda as a default..
- MySQL EOL
- MySQL 5.7 is EOL in 7 months and 3 weeks (31 Oct 2023)
- MySQL 8.0 is EOL in 3 years (30 Apr 2026)
The minimum required versions of MariaDB and MySQL for WordPress are sufficient to support the changes proposed. However, both versions are to be EOL this year which is another concern.
@OllieJones makes some good points about changing the character sets and removing indexing. But ultimately Barracuda innodb file format is available if you’re running the minimum required versions.
As for the 54.7% of sites using older MySQL versions . They’re EOL’d or soon to be EOL’d and not at minimum required version. As for the reasons these versions exist, it’s superseded by the reasoning above. The primary concern is supporting the forward movement, the bandaid has to be ripped off to enable performance changes that continue to push WordPress forward.https://github.com/WordPress/performance/issues/132#issuecomment-1463813478