WordPress powers over 40% of the internet. Behind every WordPress site sits a MySQL database with tables like wp_posts, wp_users, wp_comments, and wp_options. Whether you need to find broken posts, clean up spam, or pull custom reports, you eventually need to talk to that database.

The problem? Most WordPress users don't know SQL.

The WordPress Database Structure

Before you can query anything, you need to understand what's in there. WordPress uses a simple but effective schema:

Common WordPress Queries You Actually Need

1. Find All Published Posts from the Last 30 Days

Plain English: "Show me all published posts from the last month"

SELECT ID, post_title, post_date, post_status
FROM wp_posts
WHERE post_type = post
  AND post_status = publish
  AND post_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY post_date DESC;

2. Find Posts with Zero Comments

Plain English: "Which posts have no comments?"

SELECT p.ID, p.post_title, p.comment_count
FROM wp_posts p
WHERE p.post_type = post
  AND p.post_status = publish
  AND p.comment_count = 0
ORDER BY p.post_date DESC;

3. List All Users and Their Post Counts

Plain English: "Show me all authors and how many posts they wrote"

SELECT u.display_name, COUNT(p.ID) as post_count
FROM wp_users u
LEFT JOIN wp_posts p ON u.ID = p.post_author
  AND p.post_type = post
  AND p.post_status = publish
GROUP BY u.ID
ORDER BY post_count DESC;

4. Clean Up Spam Comments

Plain English: "How many spam comments do I have?"

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

To delete them:

DELETE FROM wp_comments WHERE comment_approved = spam;

5. Find Orphaned Post Meta

Plain English: "Find metadata entries that don't belong to any post"

SELECT pm.meta_id, pm.post_id, pm.meta_key
FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;

6. Check Active Plugins

SELECT option_value
FROM wp_options
WHERE option_name = active_plugins;

7. Find Posts by Category

Plain English: "Show all posts in the tutorials category"

SELECT p.post_title, p.post_date
FROM wp_posts p
JOIN wp_term_relationships tr ON p.ID = tr.object_id
JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms t ON tt.term_id = t.term_id
WHERE t.slug = tutorials
  AND p.post_status = publish
ORDER BY p.post_date DESC;

The Problem with Manual SQL

Every query above requires you to know exact table names, understand JOINs and date functions, connect to phpMyAdmin, and hope you don't run a destructive query. For a single query this might be manageable. For 10 different reports? It becomes a full-time job.

The Faster Way: Ask Your Database in Plain English

AI2SQL lets you skip the syntax entirely. Instead of writing complex JOINs and WHERE clauses, you describe what you want:

• "Find all draft posts older than 6 months" → instant SQL

• "Show me the top 10 most commented posts" → instant SQL

• "List users who registered this month" → instant SQL

It understands WordPress table structures, handles JOINs automatically, and never runs a destructive query without your explicit request.

WordPress Database Maintenance Queries

Delete Post Revisions (Free Up Space)

DELETE FROM wp_posts WHERE post_type = revision;

Find Slow Autoloaded Options

SELECT LENGTH(option_value) as size, option_name
FROM wp_options
WHERE autoload = yes
ORDER BY size DESC
LIMIT 20;

Remove Transients

DELETE FROM wp_options
WHERE option_name LIKE %_transient_%;

Start Querying Your WordPress Database Today

You don't need to become a SQL expert to manage your WordPress database effectively. AI2SQL bridges the gap between what you need and the technical syntax required to get it. Try it free and turn your WordPress database questions into instant answers.