SQL Queries

Getting All Data for a Post

SELECT * FROM `wp_posts` WHERE ID = 29;

SELECT * FROM  `wp_postmeta` WHERE `post_id` = 29;

Checking Rewrite Rules

SELECT *, LENGTH(option_value) AS rules_length FROM wp_options WHERE option_name = 'rewrite_rules';

SELECT LENGTH(option_value) AS rules_length FROM wp_options WHERE option_name = 'rewrite_rules';

UPDATE wp_options SET option_value = NULL WHERE option_name = 'rewrite_rules';

Deleting The WP Revision History

DELETE FROM wp_posts WHERE post_type = 'revision';

Deleting WP Auto Drafts and Trash

DELETE FROM wp_posts WHERE post_status = 'auto-draft' AND post_content = '' OR post_status = 'trash';

Deleting All WP Backups

DELETE FROM `wp_posts` WHERE `post_status` = 'auto-draft' OR `post_type` = 'revision';
DELETE FROM `wp_postmeta` WHERE `meta_key` = '_wp_old_slug';
DELETE FROM `wp_options` WHERE `option_name` = '_recently_edited';
DELETE FROM `wp_options` WHERE `option_name` LIKE '_transient_%';
UPDATE wp_options SET option_value = NULL WHERE option_name = 'rewrite_rules';

SELECT * FROM `wp_options` WHERE `option_name` LIKE '_transient_%';
SELECT * FROM `wp_postmeta` WHERE `meta_key` LIKE '%site%';
SELECT * FROM `wp_options` WHERE option_name = 'rewrite_rules';

Deleting All WP Junk and Empties

DELETE FROM wp_posts WHERE post_status = 'auto-draft' AND post_content = '' OR post_status = 'trash' OR post_type = 'revision';

View Basic Info for All Posts

SELECT ID, post_parent, menu_order, guid, post_date, post_modified, post_title, post_content, post_status, post_name, post_type, post_mime_type, comment_count
FROM wp_posts
WHERE post_status <> 'trash'
ORDER BY ID
LIMIT 100;

View Basic Info for Attachments Only

SELECT ID, post_parent AS parent_id, guid as url, post_date AS date_posted, post_modified AS date_modified,
	post_title AS title, post_content AS description, post_excerpt AS caption, post_name, post_mime_type as mime_type
FROM wp_posts WHERE post_type = 'attachment' ORDER BY ID;

SELECT * FROM wp_posts WHERE post_type = 'attachment' ORDER BY ID;
SELECT * FROM wp_posts WHERE post_parent = POST_ID ORDER BY ID;
SELECT * FROM wp_postmeta WHERE post_id = ATTACHMENT_ID;
SELECT meta_value FROM wp_postmeta WHERE post_id = POST_ID AND meta_key = '_thumbnail_id';

View Basic Info for Pages Only

SELECT ID, post_parent, guid, post_date, post_modified, post_title, post_content, post_status, post_name, comment_count
FROM wp_posts
WHERE post_type = 'page' AND post_status <> 'trash'
ORDER BY ID
LIMIT 100;

View Basic Info for Pages and Attachments Only

SELECT ID, post_parent, guid, post_date, post_modified, post_title, post_content, post_status, post_name, post_type, post_mime_type, comment_count
FROM wp_posts
WHERE post_type = 'page' OR post_type = 'attachment' AND post_status <> 'trash'
ORDER BY ID
LIMIT 100;

View Basic Info for Posts Only

SELECT ID, post_parent, guid, post_date, post_modified, post_title, post_content, post_status, post_name, post_type, post_mime_type, comment_count
FROM wp_posts
WHERE post_type = 'post' AND post_status <> 'trash'
ORDER BY ID
LIMIT 100;