Does wordpress optimize its database tables by indexing automatically?

user_4284015 0
Viewed 6010

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

wordpress query-optimization mysql
4 Answers

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.

Bill_Karwin_208600
  • 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 .
user_17668310

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.

user_2056080

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.

user_109698610
Related