Some Useful WordPress SQL Queries You Should Know

WP SQL QueriesAs an easy to use and learn blogging platform, WordPress gives its user a real easiness to get into real-blogging business of focusing their works on creating a good content for user, in a such no time.

Just like other modern Content System Management, WordPress is a very database-centrix application where the database is the heart. And the best of it is the fact that WordPress has a solid built-in functionality to self-interact with its database.

WordPress admin backend is fantastic. It does allow you to control everything in such ease and straight off. Every settings and filled-information is fed and saved into a MySQL database, in a form of its 11 default database tables. the number of database tables may grow up once you create a new ones or install a plugin that has such table-creating ability.

Although it is a rare-need for you to directly-interact with the the database, It is important for you to have a basic understanding of how your WordPress database is organized, or at least know what the language is used to play with. Thus, you won’t be in a such blind when under any circumstances and reasons you have to directly get in touch with your WordPress database.

After a long live and use of your WordPress it is a something common that your WordPress database might contain a bunch of scraps or even unseen-error on its functionality or relationships. As a result of your common actions of creating, editing, deleting, install and uninstalling things on your WordPress installation.

It is your real need to do some cleaning, repairing and optimizing works of your WordPress database. Here is some useful WordPress SQL Queries you might and should keep handy, to get those cleaning and optimizing works done.

Some Useful WordPress SQL Queries

Before you start: It’s required for you to first backup your WordPress database. And it’s always advisable to test your WordPress SQL queries on your local environment first before you run it on your live site, since a little mistake could badly mess up your WordPress functionality.

And It’s required for you to change the wp_ table prefix to match yours, in case you have custom table prefix on your WordPress installation. Otherwise your WordPress SQL Queries won’t work.

Delete Post Revision:

1. Identify:

SELECT * FROM wp_posts LEFT JOIN wp_term_relationships ON ( wp_posts.id = wp_term_relationships.object_id ) LEFT JOIN wp_postmeta ON ( wp_posts.id = wp_postmeta.post_id ) WHERE wp_posts.post_type = 'revision';

2. Delete:

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'

3. Optimize:

OPTIMIZE TABLE `wp_posts` , `wp_postmeta` , `wp_term_relationships`

Delete Orphans Post Meta

1. Identify:

SELECT *
FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL

2. Delete:

DELETE pm
FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL

Source : http://wordpress.org/support/topic/sql-query-to-delete-orphans-wp_postmeta

Delete Empty Categories

1. Identify:

SELECT
 x.count AS howmany,
 t.name AS name
FROM
 wp_terms AS t,
 wp_term_taxonomy AS x
WHERE
 t.term_id = x.term_id AND
 x.count = 0
ORDER BY
 x.count

2. Delete:

DELETE t, x
 FROM
 wp_terms AS t,
 wp_term_taxonomy AS x
 WHERE
 t.term_id = x.term_id AND
 x.count = 0

3. Optimize:

OPTIMIZE TABLE `wp_terms` , `wp_texonomy`

Source : http://www.blogtechnics.com

Delete Redundant Tags

DELETE a,b,c
FROM
    database.prefix_terms AS a
    LEFT JOIN database.prefix_term_taxonomy AS c ON a.term_id = c.term_id
    LEFT JOIN database.prefix_term_relationships AS b ON b.term_taxonomy_id = c.term_taxonomy_id
WHERE (
    c.taxonomy = 'post_tag' AND
    c.count = 0
    )

You need to adjust “database” and “prefix_” to your WordPress database name and WordPress table prefix.
Source : http://wordpress.org/support/topic/sql-query-to-delete-all-tags

Fix Gravatar Issue After Changing WP User ID

The below WordPress SQL Queries will solve the changing WordPress user ID issue that causing your gravatar no longer shows on your own blog’s comment (on an existing ones). Here’s the simple WordPress SQL Queries that will do the magic and get your gravatar back in action:

UPDATE wp_comments SET user_id='NEW_USER_ID' WHERE user_id = 'OLD_USER_ID';

Credit to : Chris Wiegman, the Author of Better WP Security Plugin

What About The Database-Repairing Work?

Repairing your WordPress database is another easy work to do, even no more simple SQL Queries needed, since you could simply do it directly through the phpMyAdmin user interface. And here’s the simple-required steps:

  1. Login to cPanel and go to phpMyAdmin,
  2. select which tables you wish to repair or simply [Check All] to select all tables.
  3. Click the [With selected] box, and choose Repair table.
  4. That’s it. And the repair-works’ done.

Cheers…!

About Sulistyo

a WordPress enthusiast, Jazz and Delta Blues addict, an Indonesian who tend to learn achieving a native-like level of English writing.

2 thoughts on “Some Useful WordPress SQL Queries You Should Know

    1. For a simple database-optimizing work you could use WP-Optimize plugin, this’ one of my favorite that do the job well.
      And for advanced user there’s plugins like SQL Executioner and WP-DBManager which allow you to run arbitrary sql queries against your WordPress database from within WordPress admin. With a bold note: You do have to know sql and use it with extreme caution.

Comments are closed.