MySQL commands to speed up your WordPress database

edited August 2016 in Web Development
phpMyAdmin is the most common way to manage a WordPress database. You can access it via logging into your server control panel. It is important to backup and save a copy of your .sql database file before you proceed with any of these commands.

To use phpMyAdmin follow the steps below:

  1. Login to phpMyAdmin panel and select your WordPress database.
  2. Click on the SQL tab which will bring you to a page with a SQL query box.
Deleting all posts from WordPress database.
DELETE
FROM wp_posts
WHERE post_type = 'revision';
Spam comments can also be deleted using the following SQL command.


DELETE FROM wp_comments WHERE comment_approved = 'spam'>
All comments awaiting approval can be deleted by using the following SQL command.

DELETE FROM wp_comments WHERE comment_approved = '0'

Even after you have removed a plugin, there is data still left inside the post_meta table which is no longer needed. Run the following query to clean this up.
delete from="" wp_postmeta="" where="" meta_key="your-meta-key" ;
After many edits to a post or page, the amount of stored revision can really start to add up. Use this command to delete all revisions.

DELETE
FROM wp_posts
WHERE post_type = 'revision';
Tagged:

Comments

  • This works great, much better than find and replace. Do you know of a way to change the domain name of a wordpress site with a single sql command?

  • edited March 2016

    Hello,

    The best way to do this is by updating wp_options, post_meta etc with the new domain using this command:

    Remember to change the url's and add https:// or http:// depending on what you use.

    UPDATE wp_options SET option_value = replace(option_value, 'olddomain.com', 'newdomain.com') WHERE option_name = 'home' OR option_name = 'siteurl';
        
        UPDATE wp_posts SET guid = replace(guid, 'olddomain.com','newdomain.com');
        
        UPDATE wp_posts SET post_content = replace(post_content, 'olddomain.com', 'newdomain.com');
        
        UPDATE wp_postmeta SET meta_value = replace(meta_value, 'olddomain.com', 'newdomain.com');
  • Thanks, works great.

Sign In or Register to comment.