Does wordpress optimize its database tables by indexing automatically?

Question

Clearly, adding indexing to Mysql tables speed up access.

Does WordPress generates this simple optimization or should I perform it manually, or use one of the many optimizations plugins?

For example: in phpMyadmin all you need is to select table optimize to generate the index

Do it manually and/or use a database optimization plugin. WordPress doesn't do this out of the box (my guess it's because this can cause issues under certain circumstances) hence the reason why many of these database optimization plugin exist. — cabrerahector, Jun 30 '19 at 18:52

score 4 · Answer 1 · answered Jun 30 '19 at 19:12

Wordpress does create many indexes when it creates tables for your database, according to what the developers thought would be the most helpful indexes.

For example, look in https://github.com/WordPress/WordPress/blob/master/wp-admin/includes/schema.php and see all the KEYs (KEY is a synonym for INDEX).

However, there could be more indexes. Indexes should be defined in any database according to the queries that are run most frequently or which need to be run most efficiently.

The Percona Blog posted an article in 2014, showing a case where their Wordpress database could have used an extra index. The blog shows exactly how they used performance analysis tools to find slow cases, and then designed an index to solve the performance issue.

"Analyzing WordPress MySQL queries with Query Analytics" (percona.com, 2014-01-16)

That case that needed the extra index was more or less specific to their usage of Wordpress. The point of the blog was not to tell people which indexes you need to add, it was to demonstrate the method of analyzing a database so you could find performance hotspots.

The right indexes for you depends on your usage of Wordpress.

score 3 · Answer 2 · answered Jun 30 '19 at 22:29

3

Do use ENGINE=InnoDB, not MyISAM
Do not use OPTIMIZE TABLE with innodb table, it almost never is worth the effort.
WP does have indexes, but not necessarily the optimal ones.
For added performance, especially when your queries involve postmeta, Change the postmeta schema .

answered Jun 30 '19 at 22:29

Rick James

135,179
13
127
222

Thanks for helpful info. Why InnoDB and not MyISAM? – Mulli Jul 01 '19 at 06:10
@Mulli see my answer to this question for one example: https://stackoverflow.com/a/17706717/20860 – Bill Karwin Jul 01 '19 at 06:25

score 3 · Answer 3 · answered Aug 03 '21 at 21:48

3

No, WordPress doesn't add database indexes automatically.

First, convert your tables to InnoDB if you haven't already.

Second, make sure you're running a recent MySQL version. (MySQL 8+, MariaDB 10.2+). If you use a hosting provider and they can't get a recent MySQL version, fire them.

Third, use a database cleaner plugin to eliminate obsolete junk (drafts of posts, transient data in wp_options, and all that) from your database. Busy WordPress instances can accumulate a lot of junk, and it just slows things down. Advanced Database Cleaner is a good one. There are many others.

Fourth, run these changes to wp_postmeta's keys to help your performance a lot.

ALTER TABLE wp_postmeta ADD UNIQUE KEY meta_id (meta_id);
ALTER TABLE wp_postmeta DROP PRIMARY KEY;
ALTER TABLE wp_postmeta ADD PRIMARY KEY (post_id, meta_key, meta_id);
ALTER TABLE wp_postmeta DROP KEY post_id;
ALTER TABLE wp_postmeta DROP KEY meta_key;
ALTER TABLE wp_postmeta ADD KEY meta_key (meta_key, post_id);

Rick James and I have published a WP plugin to do all that, and to add better indexes to other tables.

answered Aug 03 '21 at 21:48

O. Jones

103,626
17
118
172

WOW. Thanks for such a nice answer! Your plugin is need, but seems new in town and I need it for demanding production environment. What harm can it do, if it fails - if any? – Mulli Aug 06 '21 at 20:29
1

Do the database cleaning first and see how it goes. You're right,our plugin is only about a month old. Still, we've had people with 100,000 posts and 4 million postmeta entries (!!!) use it successfully. It took that person less than five minutes to convert postmeta. What's the worst imaginable thing that could happen? You'd have to restore your MySQL data from a backup. The ALTER TABLE stuff above is *exactly* what it does to postmeta. (The plugin puts your site into maintenance mode while it's changing the keys; if you run the ALTER TABLE stuff yourself it won't do that.) – O. Jones Aug 06 '21 at 21:02
Just started using the plugin. ALL went well! Bravo. But seems like no performance improvement. (Every bit of a second matters!). Its a production env. I CANNOT just restore a backup (&b loose work...) – Mulli Aug 06 '21 at 21:22
Maybe you can use the [Query Monitor](https://wordpress.org/plugins/query-monitor/) plugin (at a quiet time of day to find the worst-performing MySQL queries? I'll be happy to take a look at them. – O. Jones Aug 06 '21 at 21:26
Just to let you know that I did some perf. checks with your plugin. Then, restored the DB from backup. Previous results are better by ~20%. I still believe your work worth the effort, but probably something is still missing. – Mulli Aug 06 '21 at 21:44

score 1 · Answer 4 · answered Jun 30 '19 at 18:58

1

No, WordPress does not add indexing to mysql tables by default. As you mention you can use a plugin like WP-Optimize or optimize using phpMyAdmin:

To perform the optimization, log in to your phpMyAdmin and select the database whose tables you wish to optimize.

A list with all the database's tables will appear. Tick the tables you wish to optimize, or simply click [Check All] to select all tables.

From the [With selected:] drop-down menu choose Optimize table. This will execute the OPTIMIZE TABLE SQL query on the selected tables and they will be updated and optimized.

answered Jun 30 '19 at 18:58

TobiasM

398
4
19

3

OPTIMIZE TABLE has nothing to do with indexing. – Bill Karwin Jun 30 '19 at 19:03
*"OPTIMIZE TABLE has nothing to do with indexing"* No idea what @BillKarwin really means here as `OPTIMIZE TABLE` actually does something with existing indexes, as it will optimize (reorganizes) the physical storage index size and the table file to make it faster... But generally `OPTIMIZE TABLE` only really makes sense to run when the table changes alot by many many inserts/updates/deletes/ – Raymond Nijland Jun 30 '19 at 20:02
2

OPTIMIZE TABLE does not define new indexes, which is what I think the question was about. In general, OPTIMIZE TABLE does not make a table faster. It might defragment the table in cases like Raymond mentions, where there have been a large number of rows changed. I wouldn't expect that would make a significant difference for performance. – Bill Karwin Jun 30 '19 at 20:11

Does wordpress optimize its database tables by indexing automatically?

4 Answers4