As 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:
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';
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'
OPTIMIZE TABLE `wp_posts` , `wp_postmeta` , `wp_term_relationships`
Delete Orphans Post Meta
SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL
Delete Empty Categories
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
DELETE t, x FROM wp_terms AS t, wp_term_taxonomy AS x WHERE t.term_id = x.term_id AND x.count = 0
OPTIMIZE TABLE `wp_terms` , `wp_texonomy`
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
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';
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:
- Login to cPanel and go to phpMyAdmin,
- select which tables you wish to repair or simply [Check All] to select all tables.
- Click the [With selected] box, and choose Repair table.
- That’s it. And the repair-works’ done