Menu Search
Jump to the content X X
Smashing Conf Barcelona 2016

We use ad-blockers as well, you know. We gotta keep those servers running though. Did you know that we publish useful books and run friendly conferences — crafted for pros like yourself? E.g. upcoming SmashingConf Barcelona, dedicated to smart front-end techniques and design patterns.

8 Useful WordPress SQL Hacks

By Jean-Baptiste Jung

Over the past 10 years, the MySQL database has become incredibly popular on the Web. Every WordPress blog is driven by a MySQL database, which contains the blog’s posts, settings, comments and much more.

While plug-ins and even coding hacks can solve some problems and achieve some tasks, sometimes you don’t have any other choice than to execute SQL commands in phpMyAdmin or directly to the database via SSH. Let’s take a look at 8 useful SQL hacks for WordPress. Each section of this post presents a problem, suggests a solution and provides an explanation to help you understand the solution.

1. Creating a Backup of Your Database Link

8 Useful WordPress SQL Hacks

The problem. While the tips in the rest of this post have been tested, you should definitely not try any of them without first having a proper backup of your MySQL database.

The solution. To create a manual backup of your WordPress database, follow these simple steps:

  1. Log in to phpMyAdmin and select your WordPress database.
  2. Once done, click the “Export” button located in the horizontal menu.
  3. Choose a compression method (personally, I use gzip), and click the “Execute” button.
  4. Your browser will ask you if you want to download the backup. Of course, select “Yes,” and then store it on your hard drive.

Explanation. Note that creating a backup of your WordPress database can be more easily executed with the WP-DB-Backup1 plug-in. WordPress users should install this plug-in if they have not yet done so and create regular backups of their data.

2. Batch Delete Post Revisions Link

8 Useful WordPress SQL Hacks

The problem. Post revisions, a new WordPress 2.6 feature, can be very useful, but they also increase the size of your MySQL database. Sure, you can manually delete posts revisions, but that’s very long and boring work.

The solution. The solution to this problem is simple: we batch delete post revisions by using a simple SQL query. The result can be almost unbelievable if you have a lot of posts: Your database size will be reduced by half!

  1. Log in to phpMyAdmin and select your WordPress database.
  2. Click the “SQL” button. Paste the following code in the SQL command window:
    DELETE FROM wp_posts WHERE post_type = "revision";
  3. You’re done. Depending on how many posts you had in your WordPress database, you may have saved lots of precious space!

Code explanation. The wp_posts table has a field named post_type. This field can have one of many values, such as “post,” “page” or “revision.” When we want to get rid of post revisions, we simply run a command to delete any entry in the wp_posts table in which the post_type field is equal to “revision.”


3. Erase 5000 Spam Comments in a Second Link

8 Useful WordPress SQL Hacks

The problem. True story: a friend of mine recently created his own blog and started to promote it everywhere on the Internet. After some weeks of intensive work, he spent some days on vacation without Internet access.

When he came back home, he looked at his blog and saw… 5000+ comments awaiting moderation! Of course, most of them were spam, but he was actually about to check them all to make sure he did not delete a valid comment made by one of his regular readers.

The solution. Happily, my friend told me about his spam problem. He had already spent 45 minute manually deleting spam when I showed him this useful SQL tip.

  1. Log in to phpMyAdmin and select your WordPress database.
  2. Click the “SQL” button. Paste the following code in the SQL command window:
    DELETE from wp_comments WHERE comment_approved = '0';
  3. Goodbye bad comments! Enjoy your spam-free database!

Explanation. The wp_comments table contains a field named comment_approved, which is a boolean value (1 or 0). Approved comments have a value of 1, and comments awaiting moderation have a value of 0. By running the above command, we simply delete any comments that haven’t been approved yet.

Be careful. While this solution can be pretty useful if you have millions of spam comments to delete, it will also erase valid unapproved comments. If you don’t already use Akismet3, install it now to prevent spamming.


4. Change the Post Attribution Link

8 Useful WordPress SQL Hacks

The problem. When you installed WordPress, an “admin” account was created. Some bloggers make the mistake of using that account to write their posts, until they realize that it’s not personal at all.

The solution. Modifying author attribution on each post takes a lot of time. Happily, SQL can help you get things done:

  1. Log in to your phpMyAdmin and select your WordPress database.
  2. First, we have to get the right user IDs. To do so, open the SQL command window and execute the following command:
    SELECT ID, display_name FROM wp_users;
  3. phpMyAdmin will display a list of user IDs associated with WordPress users. Let’s say that NEW_AUTHOR_ID is the ID of the more recently created author, and OLD_AUTHOR_ID is the original admin account ID.
  4. After you swap the NEW_AUTHOR_ID and OLD_AUTHOR_ID IDs, run the following command:
    UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;
  5. That’s all. All posts previously attributed to admin are now attributed to whichever valid user you have selected.


5. Manually Reset Your Password Link

8 Useful WordPress SQL Hacks

The problem. In order to protect their blogs, people often pick strong passwords, such as u7*KoF5i8_. Of course, this is a good thing, but I have heard many stories of forgotten admin passwords.

The solution. When you lose your password, WordPress can email you a link to reset it. But if you don’t have access to the email address recorded in the WordPress database anymore, or if you prefer just running a simple command instead, here is the hack.

  1. Log in to your phpMyAdmin, select your WordPress database and open the SQL window.
  2. Insert the following command (assuming your username is “admin”):
    UPDATE `wp_users` SET `user_pass` = MD5('PASSWORD') WHERE `wp_users`.`user_login` =`admin` LIMIT 1;
  3. You’re done. Your password has been successfully replaced by whatever you inserted in space above marked “PASSWORD.”

Explanation. User passwords are stored in the wp_users table. Of course, an MD5 hash is used to secure the password.

We have to set up an “UPDATE” SQL request and use the built-in MD5() MySQL function to convert our password to MD5 and then update it. The “WHERE” clause ensures that we’re updating only the admin’s password. The same request without the “WHERE” clause would result in all passwords being updated!


6. Change Your WordPress Domain Name Link

8 Useful WordPress SQL Hacks

The problem. Although it is not recommended, you may want at some point to change your domain name while keeping your blog and its data. Because WordPress records your domain name in the database, you have to change the database in order to connect your new domain name to your WordPress blog.

The solution.

  1. You guessed it: the first thing to do is log in to your phpMyAdmin and select your WordPress database.
  2. Click the “SQL” button to open the SQL command window. In order to change your WordPress URL, execute this first command:
    UPDATE wp_options SET option_value = replace(option_value, '', '') WHERE option_name = 'home' OR option_name = 'siteurl';
  3. Then, we have to replace the relative URL (guid) of each post. The following command will do that job:
    UPDATE wp_posts SET guid = replace(guid, '','');
  4. We’re almost done. The last thing to do is a search and replace in the wp_posts table to make sure that no absolute URL is still here:
    UPDATE wp_posts SET post_content = replace(post_content, '', '');
  5. You’re done. You should be able to log in to your WordPress dashboard using your new URL.

Explanation. To easily change our WordPress domain name, I took advantage of the super-useful MySQL function “replace,” which allows you to replace one term by another.


7. Display the Number of SQL Queries on Your blog Link

8 Useful WordPress SQL Hacks

The problem. When trying to optimize your blog’s loading time, knowing the number of queries made to the database is important. In order to reduce queries, the first thing to know is how many queries are made on a single page.

The solution.

  1. This time, no need to log in to phpMyAdmin. Simply open the footer.php file in your theme and append the following lines of code:
    <?php if (is_user_logged_in()) { ?>
        <?php echo get_num_queries(); ?> queries in <?php timer_stop(1); ?> seconds.
    <?php } ?>
  2. Save the file and visit your blog. In the footer, you’ll see the number of queries made to the WordPress database as well as the time it took to make them.

Explanation. Seems that many WordPress users aren’t aware of this useful function. The get_num_queries() function returns the number of executed queries during a page load.

Note that the above code will only display the number of queries to logged-in users, because regular visitors and search engine bots don’t need to know about it. But, if you’d like to make it public, simply remove the if (is_user_logged_in()) conditional instruction.


8. Restore Your WordPress Database Link

8 Useful WordPress SQL Hacks

The problem. Let’s say, for some reason, such as a hacking or upgrade problem, you have lost your blog data or it has become corrupted. If you have a backup (and I hope you do!) you will have to import it to your WordPress database.

The solution.

  1. Log in to phpMyAdmin and select your WordPress database.
  2. Click the “Import” button in the horizontal menu.
  3. Click the “Browse” button and select the most recent database backup on your hard disk.
  4. Click the “Execute” button. If everything went well, your WordPress database is fully functional again.


Footnotes Link

  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
SmashingConf Barcelona 2016

Hold on, Tiger! Thank you for reading the article. Did you know that we also publish printed books and run friendly conferences – crafted for pros like you? Like SmashingConf Barcelona, on October 25–26, with smart design patterns and front-end techniques.

↑ Back to top Tweet itShare on Facebook


This guest post was written by Jean-Baptiste Jung, a 28-year-old blogger from Belgium, who blogs about Web Development on Cats Who Code, about WordPress at WpRecipes and about blogging on Cats Who Blog . You can stay in touch with Jean by following him on Twitter.

  1. 1

    Number 1 can also be done automatically as a cron job with the file emailed to you at a set time.

  2. 2


    December 18, 2008 3:54 pm

    Nice and useful tips (especially the DB ones). Thanks for sharing!

  3. 3

    Christopher LCP Mendes

    December 18, 2008 3:56 pm

    The Search and Replace plugin can do most of this in a user-friendly way:

    I recommend it, but be sure to use WP-Backup (mentioned in the article) before hand.

  4. 4

    Ahem if the wordpress database is a big big (more than 2Mo usually. It depends of the php.ini configuration), the phpmyadmin importer won’t work because the file won’t be uploaded.

    The best solution is and stays the mysql one, not the phpmyadmin one.
    Generate the backup :
    And to get it back, in a shell, enter the following :
    mysql -u [username] -p [password] [database_to_restore] < [backupfile]

    It requires a shell access but it’ll be working on any database, big or smaller (and the bigger are usually much more painfull with phpmyadmin than smaller ones).

  5. 5

    Wow, those are exceptional! I could have used the Password hack today without bugging my boss to reset the password.

    Going to try the Spam hack tommorow… We have about 500 comments in spam.

    Are these WP Version specific? I upgraded to 2.7 yesterday.

  6. 6

    I love the post BTW.

    What I need is to do is change the /Dir/ for uploaded images listed in posts inside the SQL data base. I have posted in the wordpress forums and recived not one peep of help.

  7. 7

    Just a tip, to backup a database from the command line, do “mysqldump -u username -p databasename > databasename.sql

  8. 8

    restoring a database would be “mysqldump -u username -p databasename < databasename.sql”

  9. 9

    Most of the hosting provider doesn’t give you shell access. So, it’s a good idea to stick to phpmyadmin whenever it’s possible.
    One trick that I do to overcome this limitation is to enable built in phpmyadmin Webserver upload/save/import directories (by editing file of phpmyadmin). Search for this string: $cfg[‘SaveDir’] and enter a directory in your web server. Next time you do any exporting, you have new option to save it to server directory, then later you can download it using F TP.

  10. 10

    Nice tips. The most useful thing I’ve ever done to my WordPress installations is to install and update them with Subversion- it’s an absolute lifesaver. Now I don’t worry when they release a new version, because it take me 20 seconds to log into my server and run “svn sw”… That would be a good follow-up post topic.

  11. 11

    Im not sure if it is md5 hash for the password. As you can see the characters generated contains characters such as * _ ^ $ and all. MD5 only contains 0-9 and A-F, or the so called hex code. correct me if Im wrong though

  12. 12

    A lot of good tips here, e.g. the one to recover the password. But some of these tips can also be done by using the WordPress admin section, e.g. changing the name shown when using the administrator-account

  13. 13

    very interesting art. ty bro.

  14. 14

    good post really…thanx :)
    P.S something similar should be done for joomla

  15. 15

    Nice article.
    An appendix for point 2:
    You can easy configure how many post revisions will be saved.
    Just insert the following code into your wp_config.php
    define('WP_POST_REVISIONS', 3);

    Explanation: WP_POST_REVISIONS specifies how many revisions will be stored in database (0=no revisions saved)

    see: article in wordpress codex

  16. 16

    I agree with Dani – A joomla version would be great :) thanks for this!

  17. 17

    Beau travail JBJ ! Continue ainsi !

  18. 18

    I love the hack that uses mysql command, that make wordpress make easier specially if you have access on shell :)

  19. 19

    # 7 is a handy one indeed. I,m using it sinds I use the WP admin.

  20. 20

    Luciano Passuello

    December 19, 2008 3:00 am

    The “Batch Delete Post Revisions” doesn’t delete the associated metadata with post revisions.
    To make sure you delete the post revision WITH all related data, use the following command:

    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)
    WHERE a.post_type = 'revision'

    (code stolen from Lester Chan).


↑ Back to top