WordPress SQL Queries for Developers, Admins & Troubleshooting

Posts and Pages

Retrieve all posts:

SELECT * FROM wp_posts WHERE post_type = 'post';

Retrieve all published posts:

SELECT * FROM wp_posts WHERE post_status = 'publish' AND post_type = 'post';

Retrieve pages only:

SELECT * FROM wp_posts WHERE post_type = 'page';

Count total posts:

SELECT COUNT(*) AS total_posts FROM wp_posts WHERE post_type = 'post';

List drafts:

SELECT ID, post_title FROM wp_posts WHERE post_status = 'draft';

Get recent 10 posts:

SELECT post_title, post_date FROM wp_posts WHERE post_type = 'post' ORDER BY post_date DESC LIMIT 10;

Users

Retrieve all users:

SELECT * FROM wp_users;

Retrieve users by role (e.g. Editor):


SELECT u.ID, u.user_login, u.user_email 
FROM wp_users u 
JOIN wp_usermeta m ON u.ID = m.user_id 
WHERE m.meta_key = 'wp_capabilities' AND m.meta_value LIKE '%editor%';

Get user’s display name and email:

SELECT display_name, user_email FROM wp_users;

List administrators:


SELECT u.ID, u.user_login 
FROM wp_users u 
JOIN wp_usermeta m ON u.ID = m.user_id 
WHERE m.meta_key = 'wp_capabilities' AND m.meta_value LIKE '%administrator%';

Comments

Retrieve all comments:

SELECT * FROM wp_comments;

Approved comments for a post:

SELECT * FROM wp_comments WHERE comment_post_ID = [POST_ID] AND comment_approved = 1;

Count comments by status:


SELECT comment_approved, COUNT(*) AS total 
FROM wp_comments 
GROUP BY comment_approved;

Top commenters:


SELECT comment_author, COUNT(*) AS comments 
FROM wp_comments 
GROUP BY comment_author 
ORDER BY comments DESC 
LIMIT 10;

Meta Data

Retrieve all meta data for a post:

SELECT * FROM wp_postmeta WHERE post_id = [POST_ID];

Retrieve featured image ID (_thumbnail_id):


SELECT meta_value 
FROM wp_postmeta 
WHERE meta_key = '_thumbnail_id' AND post_id = [POST_ID];

Taxonomies (Categories, Tags)

Retrieve all categories:


SELECT t.name 
FROM wp_terms t 
JOIN wp_term_taxonomy tx ON t.term_id = tx.term_id 
WHERE tx.taxonomy = 'category';

Retrieve all tags:


SELECT t.name 
FROM wp_terms t 
JOIN wp_term_taxonomy tx ON t.term_id = tx.term_id 
WHERE tx.taxonomy = 'post_tag';

Posts by category:


SELECT p.ID, p.post_title 
FROM wp_posts p 
JOIN wp_term_relationships tr ON p.ID = tr.object_id 
JOIN wp_term_taxonomy tx ON tr.term_taxonomy_id = tx.term_taxonomy_id 
WHERE tx.taxonomy = 'category' AND tx.term_id = [CATEGORY_ID];

Options

Retrieve site URL:

SELECT option_value FROM wp_options WHERE option_name = 'siteurl';

Retrieve all options:

SELECT * FROM wp_options;

Custom Queries

Retrieve posts of a custom post type:


SELECT * 
FROM wp_posts 
WHERE post_type = 'your_custom_post_type';

Retrieve posts with a specific custom field:


SELECT p.post_title 
FROM wp_posts p 
JOIN wp_postmeta m ON p.ID = m.post_id 
WHERE m.meta_key = 'your_meta_key' 
AND m.meta_value = 'your_meta_value';

Performance and Maintenance

Check database size:


SELECT table_name AS "Table", 
       ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" 
FROM information_schema.TABLES 
WHERE table_schema = "your_database_name";

Optimize tables:

OPTIMIZE TABLE wp_posts, wp_comments, wp_options;

Repair tables:

REPAIR TABLE wp_posts, wp_comments, wp_options;