If you’ve been working with WordPress for a while now, you know that it’s a pretty solid platform for blogging, posting content.
However, the WordPress database was clearly made by developers and there wasn’t a DBA involved. And this isn’t just WordPress, but this goes for some of the plugins as well.
I’ve got Query Monitor installed on our WordPress installation, and it pops up with slow queries every once and a while. So I figured that I’d look at the queries, and look at the indexes on the database and see what I can do about this.
Needless to say, there’s a few indexes that needed to be added.
I’m assuming that you are using the prefix wp_ on all your tables, which is the default. If you are using a prefix other than this, you’ll need to adjust this index creation scripts.
The first one is against the wp_options table.
create index dcac_option_name_autoload on wp_options (option_name, autoload);The next one to create is against wp_term_taxonomy.
create index dcac_taxonomy on wp_term_taxonomy (taxonomy, term_taxonomy_id, term_id, parent, count, description(400));The third index to be created against one of the WordPress tables is against the wp_terms table.
create index dcac_name on wp_terms (name, term_id, slug, term_group, term_order);The fourth index and fifth indexes that I’ve found that you need to create are actually against one of the Yoast plugin tables, but since most people have the Yoast plugin installed, you’ll want this index as well.
create index dcac_id_permalink_update_at on wp_yoast_indexable (id, permalink(10), updated_at);create index dcac_object_type on wp_yoast_indexable (object_type, object_sub_type);These indexes should help your WordPress system work more efficiently as it will be easier for the MySQL database that is behind your WordPress database to be able to find the data that it needs to in order to run your website.
None of these indexes are going to shave seconds on your page load times, but if they each save 100-200 milliseconds off your page load time, that’s close to a second total, and that’s a decent amount of time for queries that happen on each page load.
As I run across more indexes that need to be created, I’ll post them as I can.
If you aren’t sure how to run MySQL scripts against your database, there’s a variety of ways so if you aren’t sure how to run SQL scripts against your WordPress database, check with your hosting provider.
Looking to move your WordPress website to Microsoft Azure? The team at DCAC can help you migrate to a Cloud Services solution.
Denny
