WordPress Database Optimization MariaDB / MySQL and Other MySQL Queries

Issue: Sometimes databases, such as used by WordPress, need to be cleaned, as excessive cruft, over time, would slow down database operations.

Solution: Prune unnecessary data out of the database, using the following commands. ALWAYS, make sure to create a database BACKUP BEFORE doing anything with your database. If there is a disaster, you would then be able to restore data by importing your backup.

Clean up some editing post metadata.
_edit_lock specifies who can edit a post or page and to allow handovers to others.
_edit_last stores the ID of the user who edited the post last.

DELETE FROM wp_postmeta WHERE meta_key = "_edit_lock";
DELETE FROM wp_postmeta WHERE meta_key = "_edit_last";

Akismet can store quite a lot of data, information that is not permanently needed. That data can be cleaned up too. First check to see if there is any, then remove it.
(Run the following commands separately).

SELECT * from wp_commentmeta WHERE meta_key like "%akismet_%";
DELETE FROM wp_commentmeta WHERE meta_key LIKE '%akismet%';

If the Yoast SEO pluing has ever been installed, and you no longer use it. Then that data can be removed. First check to see if there is any data, then remove it.
(Run the following commands separately).

select * from wp_postmeta where meta_key like "%yoast%";
delete from wp_postmeta where meta_key like "%yoast%";

Clean out post revisons. The following removes all revisions from all posters.

DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )
LEFT JOIN wp_term_taxonomy d ON ( b.term_taxonomy_id = d.term_taxonomy_id)
WHERE a.post_type = 'revision'
AND d.taxonomy != 'link_category';

Remove transients. Transients temporarily store cached data using a name and timeframe (for expiry). Sometimes they can take a lot of space. The following query removes them.

DELETE FROM wp_options WHERE option_name LIKE ('%\_transient\_%')

Remove Spam comments. Sometimes there is an excessive amount, the following command deletes them using the one DB query.

DELETE FROM wp_comments WHERE comment_approved = 'spam';

Sometimes there are a lot of unused, old tags. Thes can accumulate to excessive amounts over time. The following queries will remove tags that are not associated / linked to existing posts.

DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0 );
DELETE FROM wp_term_taxonomy WHERE term_id not IN (SELECT term_id FROM wp_terms);
DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);

Similarly Pingbacks and Trackbacks (which have fallen out of standard use), is unneeded data. Remove them with the following.

DELETE FROM wp_comments WHERE comment_type = 'pingback';
DELETE FROM wp_comments WHERE comment_type = 'trackback';

Remove comment junk entries. These are orphan entries; data from comments you removed, etc. The following will remove that off the database.

DELETE FROM wp_commentmeta WHERE comment_id NOT IN ( SELECT comment_id FROM wp_comments )

Some WordPress websites do not need comment data that’s related to things like the web browser used, the IP address, and so on. If you do not need that data, you can edit the data, replacing it with “nothing” (which also reduces the size of the database).

update wp_comments set comment_agent ='' ;

How to output a list of all post URLs:

select concat('https://www.yourdomain.com/',post_name) from wp_posts where post_status='publish' and post_type='post';

How to output a list of all post URLs with title:

select post_title, concat('https://www.yourdomain.com/',post_name) from wp_posts where post_status='publish' and post_type='post';

2 thoughts on “WordPress Database Optimization MariaDB / MySQL and Other MySQL Queries”

Leave a comment

BlogLogistics