There are several ways you can optimize the MySQL database used by your WordPress site:
Optimize tables
Optimize all tables in your database:
OPTIMIZE TABLE wp_posts, wp_comments, wp_options, wp_usermeta, wp_term_relationships, wp_term_taxonomy, wp_termmeta;
Replace wp_
with the database prefix that you are using for your WordPress installation.
Repair tables
Repair any damaged tables in your database:
REPAIR TABLE wp_posts, wp_comments, wp_options, wp_usermeta, wp_term_relationships, wp_term_taxonomy, wp_termmeta;
Remove orphaned items
Remove orphaned postmeta items:
DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT id FROM wp_posts);
Remove orphaned relationship items:
DELETE tr FROM wp_term_relationships tr LEFT JOIN wp_posts wp ON wp.ID = tr.object_id WHERE wp.ID IS NULL;
Remove orphaned term items:
DELETE t FROM wp_terms t LEFT JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id WHERE tt.term_id IS NULL;
Add Indexes
Adding indexes to your database tables can improve the performance of your queries, especially for queries that use the WHERE
or JOIN
clauses. You can add indexes to your tables using the ALTER TABLE
MySQL command.
For example, to add an index to the wp_posts
table on the post_title
column, you can use the following SQL code:
ALTER TABLE wp_posts ADD UNIQUE INDEX (post_title);
This will ensure that no two rows in the wp_posts
table have the same value in the post_title
column.
You can also specify the name of the index using the INDEX
keyword, like so:
ALTER TABLE wp_posts ADD INDEX post_title_index (post_title);
This will create an index named post_title_index
on the post_title
column of the wp_posts
table.
Keep in mind that adding indexes to your database tables can improve the performance of your queries, but it can also increase the size of your database and slow down certain types of data modification queries. You should carefully consider which columns you want to index and whether the benefits of indexing outweigh the potential downsides.
Add Caching
To add caching to a WordPress database table, you can use the ALTER TABLE
MySQL command with the CACHE
option. For example, to add caching to the wp_posts
table, you can use the following SQL code:
ALTER TABLE wp_posts CACHE;
This will cause MySQL to cache the contents of the wp_posts
table for 60 minutes. After that time has elapsed, the table will be uncached and its contents will be reloaded from disk the next time the table is accessed.
EXPLAIN Queries
The EXPLAIN
keyword can be used to analyze the performance of a SELECT query and see how it is using indexes and other optimization techniques. You can use this to help identify and fix performance issues with your queries.
Here is an example of how you can use the EXPLAIN
keyword to analyze a SELECT query in WordPress:
EXPLAIN SELECT * FROM wp_posts WHERE post_type = 'page';
This will return a table with information about how the query is using indexes and other optimization techniques. For example, the type
column will indicate whether the query is using a full table scan or an index, and the key
column will show which index is being used.
Here is an example of the output you might see:
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| 1 | SIMPLE | wp_posts | ref | post_type | post_type | 767 | const | 10 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
In this example, the query is using an index (post_type
) to search for rows in the wp_posts
table where the post_type
column is ‘page’. This is more efficient than a full table scan, as it only needs to search through a portion of the table.
Keep in mind that these are just a few examples of SQL code that you can use to optimize your WordPress database. There are many other optimization techniques that you can use, and the specific optimizations that you should use will depend on your specific site and database setup.
Another interesting plugin for image optimization: https://wordpress.org/plugins/optipic/ OptiPic automatic convert image to Webp (for webp-supported browsers) and compressed/optimized png/jpeg (for webp-unsupported browsers).