WordPress Database Indexes

Introduction

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:

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

You can read more about why the change is needed in the following article.

Tables and Keys – Plum Island Media

The WordPress plugin Index WP MySQL For Speed adds these high-performance indexes (keys) to your tables in MySQL or MariaDb.

www.plumislandmedia.net

Github Issue

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.

Revisit indexes for DB performance · Issue #132 · WordPress/performance · GitHub

As discussed with @tillkruss : I was having some slow queries – specifically one query that was already described 6 years ago here: https://core.trac.wordpress.org/ticket/33885 The last comment by OllieJones https://core.trac.wordpress.o…

github.com

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.

MariaDB

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

MySQL

• 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