WordPress MySQL Useful Queries

wp-mysql-useful-queries.md

Useful WordPress MySQL Queries

This file is a collection of useful WordPress database queries. These queries range from clean up and maintenance to enhancements and recovery. Commands are listed in no particular order...

TOC

• Update The Site URL in the Database
• Get User ID by Email
• Update User Password
• Update The Site URL in the Database
• Update User Password
• Delete Post Revisions
• Find & Remove Orphaned Post Meta
• Find & Remove Orphaned Comment Meta
• Find and Remove WP Session Data
• Delete WP Transients
• Remove Unused Tags
• Disable Pingbacks and Trackbacks
• Delete Pingbacks and Trackbacks from the database
• Find WP Version In Database
• Delete All Spam Comments
• Update GUID
• Update URLs in Content
• Find Posts With The Most Spam
• Find Where Spam Comes From
• Find All Posts With A Field
• Find All Posts Where Field Is Missing
• Disable All Plugins
• Remove Encoded Characters
• Delete the Feed Cache
• Switch Themes
  
  

Update The Site URL in the Database

UPDATE wp_options SET option_value = 'http://yourwebsite.com' WHERE option_name = 'siteurl';

UPDATE wp_options SET option_value = 'http://yourwebsite.com' WHERE option_name = 'home';

Get User ID by Email

SELECT ID FROM wp_users WHERE user_email='[email protected]';

Update User Password

UPDATE wp_users SET user_pass = MD5( 'new_password' ) WHERE ID = user_id;

Delete Post Revisions

Delete all post revisions:

DELETE FROM wp_posts WHERE post_type = "revision";

Find & Remove Orphaned Post Meta

Find any orphaned Post Meta:

SELECT COUNT(pm.meta_id) as row_count FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;

Delete orphaned Post Meta:

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

Find & Remove Orphaned Comment Meta

Find any orphaned Comment Meta:

SELECT COUNT(*) as row_count FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);

Delete orphaned Comment Meta:

DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);

Find and Remove WP Session Data

Find any session data:

SELECT * FROM `wp_options` WHERE `option_name` LIKE '_wp_session_%';

DELETE FROM `wp_options` WHERE `option_name` LIKE '_wp_session_%';

Delete WP Transients

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

Remove Unused Tags

DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0 );

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

Disable Pingbacks and Trackbacks

Disable Pingbacks on your website.

UPDATE wp_posts SET ping_status = 'closed';

Delete Pingbacks and Trackbacks from the database

DELETE FROM wp_comments WHERE comment_type = 'pingback';

DELETE FROM wp_comments WHERE comment_type = 'trackback';

Find WP Version In Database

Use the following command to give you the database version number:

SELECT * FROM `wp_options` where option_name = 'db_version';

Once you have the number that it gives back to you, you can cross reference that with the table on this page of the Codex to find what version of WordPress you are running.

Delete All Spam Comments

DELETE FROM wp_comments WHERE comment_approved = 'spam';

Update GUID

UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.oldsiteurl.com', 'http://www.newsiteurl.com');

Update URLs in Content

UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://www.oldsiteurl.com', 'http://www.newsiteurl.com');

Find Posts With The Most Spam

This query is useful for finding which of your posts attract the most spam

SELECT `comment_post_ID`, COUNT(*) as amount
FROM `wp_comments`
WHERE `comment_approved` = 'spam'
GROUP BY `comment_post_ID`
ORDER BY amount DESC
LIMIT 0, 20

Find Where Spam Comes From

Use this query to find where your spam is coming from. This will show you the top 30 IPs that are spamming your site:

SELECT   `comment_author_IP`, COUNT(*) AS amount
FROM     `wp_comments`
WHERE    `comment_approved` = 'spam'
GROUP BY `comment_author_IP`
HAVING    amount > 10
ORDER BY  amount DESC
LIMIT     0, 30

If there are some big offenders here, you can safely block them in your .htaccess file.

# IP block list
order allow,deny
deny from 173.242.120.58
allow from all

Find All Posts With A Field

Find all posts (or pages) that have a specific field attached to them.

SELECT wp_posts.ID, wp_postmeta.meta_key
 FROM wp_posts
 JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
 AND wp_postmeta.meta_key = 'FIELD_NAME'
 WHERE wp_posts.post_type = 'post'
 order by wp_posts.ID asc

Find All Posts Where Field Is Missing

Use the following command if you are looking for a group of posts that are missing a specific field.

SELECT wp_posts.ID, wp_postmeta.meta_key
 FROM wp_posts
 LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
 AND wp_postmeta.meta_key = 'CUSTOM_FIELD_NAME'
 WHERE wp_postmeta.meta_key is null and wp_posts.post_type = 'post'
 order by wp_posts.ID asc 

Disable All Plugins

The following command will disable all plugins on your website. Very useful when testing websites or when encountering an error on your website.

UPDATE wp_options SET option_value = 'a:0:{}' WHERE option_name = 'active_plugins';

Remove Encoded Characters

Use the following SQL commands to remove weird characters from your website.

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, '…', '…');

Delete the Feed Cache

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

Switch Themes

Use the following command to easily switch themes without accessing the WP Admin.

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