Clear up Your WordPress Database With SQL (2019 Information)

Clear up Your WordPress Database With SQL (2019 Information)

After years of utilization, your WordPress database can comprise bizarre characters, be stuffed with information you do not want anymore, and so forth. On this article, you’ll study SQL queries to scrub up your WordPress database.

Two issues to notice: First, any of those queries ought to be preceded by a backup of your complete database. Secondly, remember to exchange the wp_ desk prefix by the prefix used in your WordPress web site set up, in any other case, the queries will not work.

How you can Run SQL Queries on Your WordPress Database

Earlier than entering into the examples, let’s take a second to take a look at how it’s doable to run SQL queries on a WordPress web site. You’ve gotten three potentialities:

  • Utilizing SSH: In case your WordPress internet hosting permits SSH connections, you may merely connect with your server and run the queries immediately into your MySQL database.
  • Utilizing PHPMyAdmin: Most WordPress internet hosting packages include cPanel and PHPMyAdmin, an online interface that means that you can execute SQL queries.
  • Utilizing a WordPress plugin: Database My Admin is a WordPress plugin that means that you can run any SQL queries towards your WordPress database from inside your WP dashboard. When you do not wish to manually run queries and simply must optimize your database, Superior Database Cleaner is likely to be a plugin to contemplate.

Clear up Your WordPress Database From Bizarre Characters

Encoding issues may be actually painful. As a substitute of manually replace all your posts, here’s a question that you could run with a purpose to clear your database from bizarre characters. Your WordPress web site shall be far more gratifying to learn in your guests.

UPDATE wp_posts SET post_content = REPLACE(post_content, '“', '"');
UPDATE wp_posts SET post_content = REPLACE(post_content, 'â€', '"');
UPDATE wp_posts SET post_content = REPLACE(post_content, '’', ''');
UPDATE wp_posts SET post_content = REPLACE(post_content, '‘', ''');
UPDATE wp_posts SET post_content = REPLACE(post_content, 'â€"', '-');
UPDATE wp_posts SET post_content = REPLACE(post_content, 'â€"', '-');
UPDATE wp_posts SET post_content = REPLACE(post_content, '•', '-');
UPDATE wp_posts SET post_content = REPLACE(post_content, '…', '...');

UPDATE wp_comments SET comment_content = REPLACE(comment_content, '“', '"');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, 'â€', '"');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '’', ''');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '‘', ''');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, 'â€"', '-');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, 'â€"', '-');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '•', '-');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '…', '...');

Supply:
http://digwp.com/2011/07/clean-up-weird-characters-in-database

Reset Administrator Password

WordPress safety is not one thing to neglect, and passwords ought to be modified each occasionally to make it possible for your WordPress web site stays safe.

As person passwords are saved inside the database, it’s doable to reset them utilizing a easy SQL question. Merely modify the question under by changing admin_username by the username of which you wish to change the password. new_password is the specified up to date password.

UPDATE `wp_users` SET `user_pass` = MD5( 'new_password' ) WHERE `wp_users`.`user_login` = "admin_username";

Notice using MySQL’s MD5 operate, which creates an MD5 hash of the desired password. WordPress safety requirements require that passwords are saved within the database as MD5 hashes.

Replace Hyperlinks to HTTPS

When you lately switched your WordPress web site or weblog to HTTPS, it is advisable replace hardcoded hyperlinks inside your articles. This can be a tedious job if you happen to do it manually, however it’s going to take you lower than a minute if you happen to use SQL queries to replace all hyperlinks contained inside your content material.

Merely replace the question under by changing yoursite.com by your URL, and run it.

UPDATE wp_posts SET post_content = change(post_content, 'http://yoursite.com', 'https://yoursite.com');

Shut Trackbacks on All Posts at As soon as

Do you utilize trackbacks and pings? These days, most individuals appear to search out them ineffective. With the intention to eliminate them, you may shut trackbacks manually, however this may eat a variety of time. Or, after all, you need to use a superb previous SQL question to carry out a database cleansing, as proven under:

UPDATE wp_posts SET ping_status = 'closed';

Mass Delete All Spam Feedback

Spam is extraordinarily widespread, and if you happen to selected to offer your readers the power to work together along with your articles, there is no doubt that a variety of spam shall be obtained.

Over time, WordPress has drastically improved the best way spam is dealt with. If spam is detected, it is not displayed in your WordPress web site immediately, however as a substitute, it is saved in a queue the place you may select whether or not to approve them or not.

In case your spam queue is lengthy, the quickest technique to mass delete all spam feedback is to run the next SQL question:

DELETE FROM wp_comments WHERE comment_approved = 'spam';

Get Rid of All Unused Shortcodes

WordPress shortcodes are very helpful and make it straightforward to embed data in your articles with out having to change any of your WordPress themes. These days, a big selection of WordPress plugins supply shortcodes that can be utilized to combine information inside the WordPress editor.

However unused shortcodes can create readability issues: When you cease utilizing a shortcode (for instance if you swap to a different WordPress theme) you will discover shortcodes in full textual content inside your content material. Here is a SQL question to take away them. Simply replace the code with the shortcode you wish to take away. I’ve used on this instance.

UPDATE wp_post SET post_content = change(post_content, '', '' ) ;

Supply: http://www.wprecipes.com/wordpress-tip-get-rid-of-unused-shortcodes

Delete Particular Submit Meta

Submit meta is information related to a selected put up. For instance, if you create a customized subject, the information is saved as meta. It may possibly then be retrieved and displayed in your WordPress web site.

When you used so as to add a selected customized subject to your posts however don’t want it anymore, you may carry out this “database cleaner” question and take away the undesired put up meta shortly and effortlessly.

DELETE FROM wp_postmeta WHERE meta_key = 'YourMetaKey';

Supply:
http://www.esoftload.data/10-sql-statements-for-wordpress

Delete All Unused Tags

A decade in the past, tags have been very fashionable in running a blog. These days, most bloggers and WordPress web site house owners stopped utilizing them. When you did, avoid wasting house in your database by cleansing it from unused tags.

DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE depend = zero );
DELETE FROM wp_term_taxonomy WHERE term_id not IN (SELECT term_id FROM wp_terms);
DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);

Delete Feed Cache

WordPress shops the feed cache within the wp_options desk. If you wish to flush the feed cache, you are able to do so by utilizing the next question:

DELETE FROM `wp_options` WHERE `option_name` LIKE ('_transientpercent_feed_%')

Supply:
http://wpengineer.com/2114/delete-all-feed-cache…

Optimize Your WordPress Database by Eradicating Transients

WordPress transients are principally a caching function: They’re used to retailer any form of information that takes a very long time to get, and subsequently are returned tremendous quick the following time you want it.

Whereas that is positively an excellent helpful function, transients can take a variety of house in your database when left unmanaged, and cut back your WordPress web site efficiency.

To carry out a complicated database cleanup, use the question under:

DELETE FROM `wp_options` WHERE `option_name` LIKE ('%_transient_%');

It’s completely secure to take away WordPress transients once in a while, as WordPress will recreate the wanted transients.

Delete All Revisions and Their Metadata

Revisions are a really helpful function, however if you happen to do not delete the numerous revisions once in a while your database will shortly develop into very huge. The next question deletes all revisions in addition to all of the metadata related to the revisions.

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);

Supply:
http://www.onextrapixel.com/2010/01/30/13-useful-wordpress-sql-queries…

Batch Delete Previous Posts

Typically, you may must delete very previous articles which might be not related. The next question will delete any article older than 600 days. This worth (outlined on line three) may be changed by any desired date in days.

If you wish to make an excellent higher model of this question, what about mixing it with the earlier one with a purpose to take away previous posts in addition to their metadata?

DELETE FROM `wp_posts`
WHERE `post_type` = 'put up'
AND DATEDIFF(NOW(), `post_date`) > 600

Take away Remark Agent

By default, when somebody leaves a reply in your weblog, WordPress saves the person agent within the database. It may be helpful for stats, however for 95% of bloggers, it’s simply ineffective. This question will change the person agent with a clean string, which might cut back your database measurement in case you have a lot of replies.

replace wp_comments set comment_agent ='' ;

Replace Admin Electronic mail Deal with

All WordPress information is saved inside database tables, which signifies that it could actually very simply be up to date utilizing SQL queries.

If it is advisable replace the admin e-mail, right here is the question to do it.

UPDATE `wp_users` SET `user_email` = "new_email_address" WHERE `wp_users`.`user_login` = "admin";

After all, this question can be utilized to replace any e-mail contained inside your wp_users desk. Merely change admin with the username of the account you wish to change the e-mail handle.

Batch Disable All WordPress Plugins

It may possibly occur defective plugin breaks your WordPress web site. Even worst, relying on how severe the error is, you may find yourself being unable to entry your wp_admin space the place you possibly can have been capable of deactivate the WordPress plugin inflicting an error.

In that case, the most effective factor to do is to deactivate all of the plugins utilizing the next SQL question:

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

More than likely, this may clear up the error and mean you can log again into your WP dashboard, the place you may re-activate plugins one after the other and establish which one was inflicting issues.

Swap WordPress Themes utilizing SQL

WordPress shops your web site settings inside the wp_options database desk. Due to this fact, your energetic WordPress theme may be modified utilizing a easy SQL question.

This may be very helpful in case your energetic theme has an error that forestalls you from accessing your admin dashboard. The next question will restore WordPress’ Twenty Nineteen because the energetic theme.

UPDATE wp_options SET option_value = 'twentynineteen' WHERE option_name = 'template' or option_name = 'stylesheet';

Change Creator Attribution on All Posts at As soon as

Do it is advisable change writer attribution on many posts? If sure, you do not have to do it manually. Here is a helpful question to do the job for you.

The very first thing to do is to retrieve the IDs of WordPress customers. As soon as logged into MySQL, use the next SQL question to get an inventory of customers, in addition to their IDs:

SELECT ID, display_name FROM wp_users;

Let’s contemplate that NEW_AUTHOR_ID is the ID of the brand new writer, and OLD_AUTHOR_ID is the previous writer ID. Run this question to assign a brand new writer to all articles presently assigned to OLD_AUTHOR_ID.

UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;

As soon as this question has been executed, all posts from the previous writer now seem to have been written by the brand new writer.

Often Requested Questions

How Secure Is It to Run These Queries on a Stay Website?

All of the above queries have been examined and are completely secure. That being stated, there is no method to return when a SQL question has been executed. For that reason, you must at all times have a recent backup of your database. This may be completed by utilizing a WordPress plugin like WP Database Backup or by utilizing backups offered by your WordPress internet hosting.

Can I Use WordPress Plugins As a substitute of Operating Queries?

After all. Many WordPress plugins present superior cleansing choices for WordPress databases. Superior Database Cleaner might be the most well-liked WordPress plugin for database optimization and efficiency.

Subjects:

database tutorial
,
sql
,
wordpress database
,
sql queries
,
database
,
tutorial

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.