10+ Life Saving WordPress SQL Queries

 WordPress SQL Queries What is?

WordPress SQL Queries square measure most powerful and economical means for any WordPress Geeks.A CMS(content management system) website could also be assist you to make an internet site with none committal to writing. though you’ll be able to produce web sitean internet site a web site and style this website while not committal to writing however there’s a limitation. generally you will have to be compelled to fix for a particular drawback. in this cases , operating directly on the information will be a true life-saver. i’m progressing to justify prime 10+ life saving WordPress SQL queries. Optimize WordPress site website web website is additionally vital for any WordPress site.

How to execute WordPress SQL Queries   10+ life saving WordPress SQL Queries

If you don’t understand execute SQL(Structured question Language) queries than this some line for you. WordPress SQL Queries have to be compelled to be dead at intervals the MySQL statement interpreter or an internet interface like the favored phpmyadmin. head to your WordPress host website, it’s going to be CPanel or FTP shopper server or 1and1 server. As we have a tendency to square measure operating with WordPress, we must always note that the WordPress SQL Queries public executioner plugin helps with a simple thanks to United Statese interface that enables us to run WordPress SQL Queries directly on your WordPress diary dashboard.
Manually amendment your arcanum

This drawback may be a nice drawback on behalf of me. I forget my arcanum if I don’t use it with seven or eight days. If you get this drawback any time you’ll be able to use this for reset your arcanum. If you lost your diary admin arcanum, the sole resolution is to make a brand new one directly in your MySQL information. look into the subsequent question. Mind that, we have a tendency to use the MD5() MySQL operate to show our arcanum into an MD5 hash.

UPDATE 'wp_users' SET 'user_pass' = MD5('PASSWORD') WHERE 'user_login' ='admin' LIMIT 1;

Transfer posts from one user to another

You can try this manually by WordPress from your user panel. however as i’m currently talking with queries i will be able to show you the way you’ll try this with queries. As most journalger use an easy name at the primary time of blog however once he/she needs to use his/her real name he/she have to be compelled to amendment the post author conjointly. you’ll simply transfer your previous “admin” posts to your new account with the WordPress SQL Queries below. however want to need the user id each your previous and new accounts.

UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;

Transfer posts from one user to another

Delete post revisions and meta associated to those revisions

Post revisions area unit terribly helpful to a multi author diary. however the matter of post revisions is certainly the quantity of information records it creates. as an example, your diary contains fifty posts, that has ten revisions every post. Than you may find yourself with five hundred records within the wp_posts tables,while solely fifty area unit necessary.

DELETE a,b,c FROM wp_posts a WHERE a.post_type = 'revision' LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id);

Batch delete spam comments

If you haven’t put in Akismet and betting on your journal quality, you will find yourself with one thousand, 2000 or perhaps ten,000+ comments to moderate.You can pay an entire day to moderate the heap, otherwise you will use this life-saving question to delete all unapproved comments.

DELETE from wp_comments WHERE comment_approved = '0';

Find unused tags

Tags ar recorded on the wp_terms table of your phpadmin. If for a few reason a tag has been created however isn’t used any longer, it stays within the table. this question can allow you to understand that tags ar on the wp_terms table while not getting used anyplace on your diary. you’ll delete those safely.

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 is a requirement grasp for anyone who’s operating with MySQL databases. The MySQL operate replace  helps you to specify a field name, a string to seek out, and a replacement string. Once the question is dead, all occurrences of the string to switch are going to be replaced by the replacement string. just in case of a WordPress web log, this may be helpful to batch replace a mistake (For example those who repeatedly decision the computer code WordPress…) or an email address.

UPDATE table_name SET field_name = replace( field_name, 'string_to_find', 'string_to_replace' ) ;

Get a list of your commentators emails

If you ever received uninvited emails from web log your antecedently commented. i’m a victim of this. the actual fact is that obtaining a listing of emails from your commentators is extraordinarily straightforward exploitation the subsequent question. The DISTINCT parameter can certify that we’ll solely get every email once, though the user commented over once.
Please note that this is often solely an indication of concept: don’t send spam email to traveller.

SELECT DISTINCT comment_author_email FROM wp_comments;

Disable all your plugins at once

When your journal goes wrong. you have got to require action quickly. Plugins square measure usually the most supply of issues. If you’ll be able to disable all plugin may be a second this could save plenty of your time. simply play with the subsequent question.

UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins';

Delete all tags

Tags square measure recorded within the wp_terns tables, beside classes and taxonomies If you would like to get rid of all tags, you can’t merely empty or delete the wp_terms. If you wish to try and do this run the subsequent question. . it’ll take away all tags and relationships between tags and posts, whereas effort classes 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

Post meta is formed by plugin and custom fields. this is often be terribly helpful however on the opposite hand it can quickly grow your information size. the subsequent question can show you the all records within the postmeta table that doesn’t have corresponding records within 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

In the long haul of web logging I even have detected that each recent blogger disable comments on the recent post of their blog. you’ll do that into your web log by the subsequent question. This WordPress SQL question question can shut comments on all posts revealed before Jan one, 2013.

UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '20013-01-01' AND post_status = 'publish';

Replace commentator URL

Let’s say you previously own a web site and used its address in your comments to come up with backlinks to the current site. If you sell the location, you’ll be able to simply replace the recent address by your new website address. merely run the subsequent question 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 is associate another exemplar of the replace() function. this question can replace the e-mail address provided within the comments field, by a replacement one. If you would like this ,just run this question.

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

In the long-term of blogging I even have a good information of spammers. They even left comments with new plan. the subsequent question can delete all comments with a particular address. The “%” signs mean that any address containing the string among the sharp signs are going to be deleted.

DELETE from wp_comments WHERE comment_author_url LIKE "%h2cweb%" ;