Indexing also autoload in wp_options

Standard WordPress databases already have an index on autoload, and this plugin leaves it in place. The table’s indexes after this plugin handles them are these.

	PRIMARY KEY (option_name)
	UNIQUE KEY option_id (option_id)
	KEY autoload (autoload)

There was a time when WordPress installations lacked that index. But no more. The article you linked is for operators of those legacy installations.

There are basically two query patterns WordPress uses to hit the options table. One is used to load all the autoload options initially.

SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'

The other, far more common, pattern is this.

SELECT option_value FROM wp_options WHERE option_name = 'something'

along with UPDATE, INSERT, and DELETE variants of that pattern.

These indexes are designed to accelerate the common query patterns by putting the option_name in the primary key’s clustered index.

An earlier version of this plugin set these indexes.

	PRIMARY KEY (autoload, option_id)
	UNIQUE KEY option_id (option_id)
	UNIQUE KEY option_name (option name)

This puts the autoload column in the clustered index, which makes the initial load a bit faster. But, we learned it is much less frequent than the second query pattern. In fact, when a site uses a persistent object cache (redis, memcached) the initial load operation becomes very infrequent. So we changed the clustered index in V1.4.

You can learn more here. https://www.plumislandmedia.net/index-wp-mysql-for-speed/tables_and_keys/

• This reply was modified 3 years ago by OllieJones.

Thank you Ollie!

As you touched on the clustered indexes, here are a couple of queries that are actually currently the “slowest” queries on my site, albeit not frequent:

SELECT term_id, meta_key, meta_value FROM lnp_termmeta WHERE term_id IN (ilonglist) ORDER BY meta_id ASC

and

SELECT post_id, meta_key, meta_value FROM lnp_postmeta WHERE post_id IN (ilonglist) ORDER BY meta_id ASC

Is it worthwhile to add a clustered index for term/post_id, meta_key, meta_value fields?

(Edit: also queries like this
SELECT lnp_posts.* FROM lnp_posts WHERE 1=1 AND lnp_posts.ID IN (ilist) AND lnp_posts.post_type = s AND ((lnp_posts.post_status = s)) ORDER BY lnp_posts.menu_order, lnp_posts.post_date DESC

Where, optimally, we should add a clustered index for ID, post_type, post_status, menu_order, post_date or, at least for just ID, post_type, post_status)

• This reply was modified 3 years ago by dimitrisv. Reason: additional context

Finally, how about intra-table indexes to cater for (also “slow”) queries in two/three affecting 3 tables like:

SELECT DISTINCT t.*, tt.* FROM lnp_terms AS t LEFT JOIN lnp_termmeta ON ( t.term_id = lnp_termmeta.term_id AND lnp_termmeta.meta_key=s) INNER JOIN lnp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN lnp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN (s) AND tr.object_id IN (i) AND ( ( lnp_termmeta.meta_key = s OR lnp_termmeta.meta_key IS NULL ) ) ORDER BY lnp_termmeta.meta_value+0 ASC, t.name ASC

and

SELECT t.*, tt.* FROM lnp_terms AS t INNER JOIN lnp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN lnp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN (s) AND tr.object_id IN (i) ORDER BY t.term_order DESC

and

SELECT t.term_id, tt.parent, tt.count, tt.taxonomy FROM lnp_terms AS t INNER JOIN lnp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN lnp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN (s) AND tr.object_id IN (i) ORDER BY t.term_order ASC

• This reply was modified 3 years ago by dimitrisv. Reason: additional context

The plugin already adds PRIMARY KEY (term_id, meta_key, meta_id) as the clustered index on wp_termmeta, and analogous primary keys on wp_postmeta and wp_usermeta.

meta_value has the LONGTEXT data type so it’s unsuitable for any kind of indexing except prefix indexing.

The performance issue with those IN (long, long, long, list, of, id, values, blah, blah, blah) is the need to iterate the index lookups. That long-list query pattern is baked into WordPress core. The good news? Those result sets are used to populate WordPress’s object cache. So if you use a persistent (redis, memcached) object cache they aren’t repeated much.

You only get one clustered index per table: it’s the primary key. You can, and we do, add covering indexes for other common query patterns whereever possible.

As far as we can tell, the term / taxonomy (t.*) tables’ indexes match their query patterns fairly well. So we didn’t optimize them. SELECT t.*, tt.* as a query pattern is sloppy database programming, but again it’s baked into WordPress core.

Thanks for your interest! Keep the ideas coming!

• This reply was modified 3 years ago by OllieJones.