Before taking any step make a backup of your current WordPress database. How to make a backup of your WP database? You can use different WordPress Plugins like Smart Backup and WP-DB_Backup. Alternatively, you can use phpMyAdmin.
Smart Backup is a complete WordPress solution for database backup and restores operations. You can create backups of your complete WordPress installation, files only, or database only. Backups can be restored with one click.
Where to run WordPress SQL Queries
Hope you know where to run SQL Queries, If not follow these steps: Login to phpMyAdmin panel and select your WordPress database. After selecting your database, Click on the SQL tab which will bring you to a page with a SQL query box.
There are some plugins and script that will increase you SQL experience:
SQL Executionner plugin provides an easy-to-use interface that allows you to run SQL queries directly on your WordPress blog dashboard.
Super MySQL Terminal: Super MySQL Terminal is a PHP MySQL shell emulator. You can work with your share-hosted remote MySQL like your localhost.
SQL Comparer – Synchronize your database versions: Compare and synchronize two SQL database schemas and see the difference between them. A beneficial tool when dealing with different versions of a database.
SQL Document Generator: SQL Document Generator is a set of classes for creating documents in various formats based on SQL queries.
Enhanced SQL Web Console for MySQL: This script helps you helps you fire queries from the database and export the resultset with a browser on the fly.
Features
1). Drag-drop to use, no setup needed.
2). Smart bookmark feature, you can bookmark your favorite scripts and pickup them to use anytime.
3). Export the query resultset to CSV , Excel, XML , HTML formats.
4). Read-only mode avoiding writable scripts executing.
and more…
MySQL Database Navigation and Pagination: If you are working with hosting MySQL databases, and pick up data from them frequently, this script may make your life easy working with them.
CreativeTable: CreativeTable it’s a PHP class that builds a table based on the data retrieved by an SQL query or a 2D Array. It has lots of options, and it’s very, very configurable.
LogPress: This plugin will help you to debug and speedup development of WordPress plugins and themes. Besides logging PHP error it can log a bunch of other things to help you make your website better.
Most Important WordPress SQL Queries:
How to: Manually reset your WordPress password
What to do if you lost your WordPress password? The easier is to use PhpMyAdmin and execute a simple SQL query to update it. Here’s how to proceed.
UPDATE 'wp_users' SET 'user_pass' = MD5('PASSWORD') WHERE 'user_login' ='admin' LIMIT 1;
Change author attribution on all posts at once
Some bloggers makes the mistake to write their first posts under the name “admin”, until they realize that it’s absolutely not personal. But then, modifying author attribution on each post takes a lot of time. Happilly, here’s a recipe to batch modify author attribution.
UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;
Delete post revisions and meta associated to those revisions
Post revisions are very useful, especially in the case of a multi author blog. However, the problem of post revisions is definitely the number of database records it creates.
define('WP_POST_REVISIONS', false);
You can also delete all post revisions by running this query in phpMyAdmin:
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'
Batch delete spam comments
Best way install Akismet! if you want delete more than 6000 spam comments by using this command of line.
DELETE from wp_comments WHERE comment_approved = '0';
Identify Unused Tags
In a WordPress database, if you run a query to delete old posts manually from MySQL, the old tags will remain and appear in your tag cloud/listing. This query allows you to identify the unused tags.
SELECT * From wp_terms wt INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy='post_tag' AND wtt.count=0;
Find and Replace Data
This tip isn’t specific to WordPress and is a must know for anyone who’s working with MySQL databases.
UPDATE table_name SET field_name = replace( field_name, 'string_to_find', 'string_to_replace' ) ;
Get a list of your commentators emails
Over a period of time, your blog will have received many comments. These comments will include the email addresses left by the commenter. You can retrieve all these emails for your mailing list without any duplicate.
SELECT DISTINCT comment_author_email FROM wp_comments;
Disable all your plugins at once
Sometimes, for exemple when you have to upgrade your blog, you need to disable all your plugins. Depending to how much plugins you’re using, it can takes a lot of time and be kinda boring.
UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins';
SQL Query to delete all tags
This piece of code will remove all tags and relationships between tags and posts, while leaving categories and taxonomies intact.
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
);
List unused post meta
The following query will show you all the records in the postmeta table that doesn’t have corresponding records in the post table.
SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;
Disable comments on older posts
This code will automatically close comments on posts which are too old. This SQL query will close comments on all posts published before January 1, 2009.
UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2009-01-01' AND post_status = 'publish';
Replace commentator url
This SQL query easily replace the old url by your new site url. Simply run this query and you’ll be done!
UPDATE wp_comments SET comment_author_url = REPLACE( comment_author_url, 'http://oldurl.com', 'http://newurl.com' );
Replace commentator email address
This query will replace the email address provided in the comments field, by a new one.
UPDATE wp_comments SET comment_author_email = REPLACE( comment_author_email, 'old-email@address.com', 'new-email@address.com' );
Delete all comments with a specific url
The following query will delete all comments with a specific url. The “%” signs means that any url containing the string within the % signs will be deleted.
DELETE from wp_comments WHERE comment_author_url LIKE "%sitetobeDel%" ;