Delete posts in WordPress DB to light up the database

Overview

When managing WordPress installations, database bloat from accumulated posts, revisions, and metadata can significantly impact performance. This guide provides methods to safely reduce database size while preserving critical content and maintaining data integrity.

Why Database Optimization Matters

Query Performance: Smaller datasets result in faster query execution times
Backup Size: Reduces backup file size and backup duration
Memory Usage: InnoDB buffer pool operates more efficiently with less data
Server Resources: Decreases disk I/O and CPU utilization

Prerequisites

Direct database access (MySQL/MariaDB)
Database admin credentials
Understanding of SQL DELETE operations
CRITICAL: Full database backup before execution
Read-only test environment recommended for validation

Important Considerations

Data Integrity

WordPress posts are interconnected with multiple related tables through foreign key relationships and referential integrity constraints:

Related Table	Content	Impact	Relationship Type
`wp_postmeta`	Post metadata, custom fields	Orphaned records if parent deleted	One-to-Many (post_id)
`wp_comments`	Comments on posts	May reference deleted posts	One-to-Many (comment_post_ID)
`wp_term_relationships`	Category/tag associations	Foreign key constraints	Many-to-Many (object_id)
`wp_links`	Related post links	May break internal links	One-to-Many (link_target)
`wp_postmeta`	Custom fields, SEO metadata	Orphaned metadata records	One-to-Many (post_id)
`wp_term_relationships`	Post taxonomies	Breaks category/tag counts	Many-to-Many (object_id)

Warning: Deleting posts orphans their associated comments and metadata unless handled with cascading deletes.

Understanding WordPress Database Schema

The wp_posts table is the central entity in WordPress's database architecture:

sql

1-- Core wp_posts table structure
2CREATE TABLE wp_posts (
3  ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
4  post_author bigint(20) unsigned NOT NULL DEFAULT '0',
5  post_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
6  post_date_gmt datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
7  post_content longtext NOT NULL,
8  post_title text NOT NULL,
9  post_excerpt text NOT NULL,
10  post_status varchar(20) NOT NULL DEFAULT 'publish',
11  comment_status varchar(20) NOT NULL DEFAULT 'open',
12  ping_status varchar(20) NOT NULL DEFAULT 'open',
13  post_password varchar(255) NOT NULL DEFAULT '',
14  post_name varchar(200) NOT NULL DEFAULT '',
15  to_ping text NOT NULL,
16  pinged text NOT NULL,
17  post_modified datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
18  post_modified_gmt datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
19  post_content_filtered longtext NOT NULL,
20  post_parent bigint(20) unsigned NOT NULL DEFAULT '0',
21  guid varchar(255) NOT NULL DEFAULT '',
22  menu_order int(11) NOT NULL DEFAULT '0',
23  post_type varchar(20) NOT NULL DEFAULT 'post',
24  post_mime_type varchar(100) NOT NULL DEFAULT '',
25  comment_count bigint(20) NOT NULL DEFAULT '0',
26  PRIMARY KEY (ID),
27  KEY post_name (post_name(191)),
28  KEY type_status_date (post_type,post_status,post_date,ID),
29  KEY post_parent (post_parent),
30  KEY post_author (post_author)
31) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Key Indexes for Performance:

PRIMARY KEY (ID): Clustered index for row lookup
type_status_date: Composite index for filtered queries
post_name: Index for permalink resolution
post_author: Foreign key reference to wp_users

InnoDB Storage Engine Characteristics

WordPress uses InnoDB by default, which has specific implications for bulk deletions:

Transaction Isolation:

Default level: REPEATABLE READ
DELETE operations acquire exclusive locks on affected rows
Gap locks prevent phantom reads in range scans
Lock escalation can occur with large batch operations

Row-Level Locking Behavior:

sql

1-- Check current transaction isolation level
2SELECT @@transaction_ISOLATION;
3
4-- View active InnoDB locks during deletion
5SELECT * FROM performance_schema.data_locks
6WHERE OBJECT_SCHEMA = 'wordpress_db'
7AND OBJECT_NAME = 'wp_posts';
8
9-- Monitor lock wait statistics
10SELECT * FROM performance_schema.data_lock_waits;

Buffer Pool Impact:

Deleted rows remain in buffer pool until evicted
OPTIMIZE TABLE forces buffer pool refresh
Monitor buffer pool efficiency:

sql

1-- Check InnoDB buffer pool statistics
2SHOW ENGINE INNODB STATUS\G
3
4-- Buffer pool hit ratio (target: >99%)
5SELECT
6    VARIABLE_VALUE AS buffer_pool_reads,
7    (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
8     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') AS read_requests,
9    ROUND(
10        (1 - VARIABLE_VALUE /
11         (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
12          WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')) * 100,
13        2
14    ) AS hit_ratio_percent
15FROM information_schema.GLOBAL_STATUS
16WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads';

Transaction Isolation Levels Explained:

MySQL InnoDB supports four transaction isolation levels as defined by SQL standards. Understanding these is crucial for bulk deletion operations:

Isolation Level	Dirty Reads	Non-Repeatable Reads	Phantom Reads	Performance	Use Case
`READ UNCOMMITTED`	Possible	Possible	Possible	Highest	Analytics (low accuracy requirements)
`READ COMMITTED`	Prevented	Possible	Possible	High	Most web applications
`REPEATABLE READ` (default)	Prevented	Prevented	Possible*	Medium	WordPress default (InnoDB gap locks)
`SERIALIZABLE`	Prevented	Prevented	Prevented	Lowest	Financial transactions

*InnoDB uses gap locks in REPEATABLE READ to prevent phantom reads.

Lock Types in DELETE Operations:

sql

1-- Next-key locks: Combination of record lock + gap lock
2-- Applied during DELETE operations in REPEATABLE READ isolation
3
4-- Example: Deleting posts with ID > 100 and ID < 200
5DELETE FROM wp_posts WHERE ID > 100 AND ID < 200;
6
7-- InnoDB acquires:
8-- 1. Record locks on existing rows (101, 102, ..., 199)
9-- 2. Gap locks on ranges between rows
10-- 3. Next-key locks = record lock + gap lock before record
11
12-- View current lock waits
13SELECT
14    waiting_trx_id,
15    waiting_pid,
16    waiting_query,
17    blocking_trx_id,
18    blocking_pid,
19    blocking_query,
20    wait_started,
21    wait_age,
22    locked_table,
23    locked_index,
24    locked_type
25FROM sys.innodb_lock_waits;
26
27-- Check lock timeout setting
28SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
29
30-- Adjust lock timeout for bulk operations (default: 50 seconds)
31SET SESSION innodb_lock_wait_timeout = 300;

Deadlock Detection and Prevention:

sql

1-- Enable deadlock detection (enabled by default)
2SHOW VARIABLES LIKE 'innodb_deadlock_detect';
3
4-- View recent deadlocks
5SHOW ENGINE INNODB STATUS\G
6
7-- Deadlock prevention strategies for bulk deletes:
8
9-- Strategy 1: Order operations consistently
10-- Always delete from parent to child tables in same order
11DELETE FROM wp_postmeta WHERE post_id IN (SELECT ID FROM temp_delete_list);
12DELETE FROM wp_term_relationships WHERE object_id IN (SELECT ID FROM temp_delete_list);
13DELETE FROM wp_posts WHERE ID IN (SELECT ID FROM temp_delete_list);
14
15-- Strategy 2: Use smaller transactions
16-- Break large deletes into smaller batches
17DELIMITER $$
18CREATE PROCEDURE safe_batch_delete(IN batch_size INT, IN max_id INT)
19BEGIN
20    DECLARE current_id INT DEFAULT 0;
21    DECLARE rows_deleted INT;
22
23    delete_loop: LOOP
24        START TRANSACTION;
25
26        DELETE FROM wp_posts
27        WHERE ID > current_id
28        AND ID <= current_id + batch_size
29        AND ID NOT IN (SELECT id FROM posts_to_keep)
30        LIMIT 1000;
31
32        SET rows_deleted = ROW_COUNT();
33        SET current_id = current_id + batch_size;
34
35        COMMIT;
36
37        -- Exit if no more rows or reached max_id
38        IF rows_deleted = 0 OR current_id >= max_id THEN
39            LEAVE delete_loop;
40        END IF;
41
42        -- Brief pause to allow other transactions
43        DO SLEEP(0.1);
44    END LOOP;
45END$$
46DELIMITER ;
47
48-- Execute the procedure
49CALL safe_batch_delete(1000, 100000);
50
51-- Monitor deadlock rate
52SELECT
53    VARIABLE_VALUE AS deadlock_count
54FROM information_schema.GLOBAL_STATUS
55WHERE VARIABLE_NAME = 'Innodb_deadlocks';

MySQL Configuration for Bulk Operations

Before performing large-scale deletions, optimize MySQL configuration for bulk operations:

sql

1-- Temporarily increase delete buffer for large operations
2SET GLOBAL innodb_change_buffering = 'all';
3
4-- Adjust transaction commit behavior (use with caution)
5-- 0 = Write and flush once per second (faster, less safe)
6-- 1 = Write and flush at each commit (default, safest)
7-- 2 = Write at commit, flush once per second (balanced)
8SET GLOBAL innodb_flush_log_at_trx_commit = 2;
9
10-- View current InnoDB configuration
11SHOW VARIABLES LIKE 'innodb%';
12
13-- Check adaptive hash index status
14SHOW ENGINE INNODB STATUS\G
15
16-- Monitor undo log size during operations
17SELECT
18    SUBSYSTEM,
19    NAME,
20    COUNT
21FROM performance_schema.memory_summary_global_by_event_name
22WHERE NAME LIKE '%innodb%undo%';

Important Configuration Parameters:

Parameter	Default	Recommended for Bulk Delete	Description
`innodb_buffer_pool_size`	128M	70% of RAM	InnoDB cache size for data/indexes
`innodb_log_file_size`	48M	512M-2G	Redo log file size
`innodb_flush_log_at_trx_commit`	1	2 (temp)	Transaction durability vs performance
`innodb_io_capacity`	200	2000+ (SSD)	I/O operations per second
`innodb_change_buffering`	all	all	Buffer INSERT/UPDATE/DELETE operations

Restore safe settings after bulk operations:

sql

1SET GLOBAL innodb_flush_log_at_trx_commit = 1;
2SET GLOBAL innodb_change_buffering = 'all';

Backup Strategy

bash

1# Full database backup before any DELETE operations
2mysqldump -u username -p database_name > wordpress_backup_$(date +%Y%m%d_%H%M%S).sql
3
4# Specific table backup
5mysqldump -u username -p database_name wp_posts > wp_posts_backup.sql
6
7# Backup with compression for large databases
8mysqldump -u username -p database_name | gzip > wordpress_backup_$(date +%Y%m%d_%H%M%S).sql.gz
9
10# Backup with compression and progress indicator
11mysqldump -u username -p database_name | pv | gzip > wordpress_backup_$(date +%Y%m%d_%H%M%S).sql.gz
12
13# Incremental backup using binary logs (ensure binlog is enabled)
14mysqldump -u username -p --single-transaction --flush-logs --master-data=2 \
15  database_name > wordpress_full_$(date +%Y%m%d_%H%M%S).sql
16
17# Verify backup integrity
18gunzip -c wordpress_backup_20260201_120000.sql.gz | mysql -u username -p test_restore_db
19
20# Backup multiple related tables together
21mysqldump -u username -p database_name \
22  wp_posts wp_postmeta wp_comments wp_term_relationships \
23  > wordpress_posts_backup_$(date +%Y%m%d_%H%M%S).sql

Backup Best Practices:

Full vs. Selective Backups:

Full backup: All tables and data
Selective: Only affected tables (wp_posts, wp_postmeta, wp_comments)
Binary log backups for point-in-time recovery

Backup Validation:

bash

1# Test backup file integrity
2if gunzip -t wordpress_backup.sql.gz; then
3    echo "Backup file is valid"
4else
5    echo "Backup file is corrupted!"
6fi
7
8# Verify record count before deletion
9mysql -u username -p -e "SELECT COUNT(*) FROM wp_posts;" database_name

Point-in-Time Recovery Setup:

sql

1-- Enable binary logging in my.cnf or my.ini
2[mysqld]
3log-bin=mysql-bin
4binlog_format=ROW
5expire_logs_days=7
6
7-- Verify binary logging is enabled
8SHOW VARIABLES LIKE 'log_bin';
9
10-- List binary logs
11SHOW BINARY LOGS;
12
13-- View binary log events
14SHOW BINLOG EVENTS IN 'mysql-bin.000001';

Safe Deletion: Retain First 100 and Last 100 Posts

Delete posts in WordPress DB except 100 first and last 100

sql

1DELETE FROM wp_posts
2WHERE ID NOT IN (
3    SELECT ID FROM (
4        -- Retain the first 100 rows
5        SELECT ID FROM wp_posts ORDER BY ID ASC LIMIT 100
6    ) AS first_100
7    UNION
8    SELECT ID FROM (
9        -- Retain the last 100 rows
10        SELECT ID FROM wp_posts ORDER BY ID DESC LIMIT 100
11    ) AS last_100
12);

Understanding the Query Mechanics

This query uses a derived table pattern to circumvent MySQL's limitation where you cannot directly reference the same table in a DELETE and subquery. The nested SELECT statements create temporary result sets that MySQL treats as separate entities.

Key Technical Points:

Subquery Execution Order: MySQL first evaluates the innermost SELECT statements to build the first_100 and last_100 derived tables
UNION Operator: Combines both result sets while automatically removing duplicates (critical if a database has fewer than 200 posts)
NOT IN Performance: For small result sets (<1000 rows), NOT IN performs adequately. For larger operations, consider LEFT JOIN ... WHERE ... IS NULL pattern
Transaction Isolation: This operation acquires row-level locks on InnoDB tables during execution

Pre-Deletion Validation Queries

Before executing deletion, verify which posts will be affected:

sql

1-- Count posts that will be deleted
2SELECT COUNT(*) AS posts_to_delete
3FROM wp_posts
4WHERE ID NOT IN (
5    SELECT ID FROM (
6        SELECT ID FROM wp_posts ORDER BY ID ASC LIMIT 100
7    ) AS first_100
8    UNION
9    SELECT ID FROM (
10        SELECT ID FROM wp_posts ORDER BY ID DESC LIMIT 100
11    ) AS last_100
12);
13
14-- Preview posts that will be deleted
15SELECT ID, post_title, post_type, post_status, post_date
16FROM wp_posts
17WHERE ID NOT IN (
18    SELECT ID FROM (
19        SELECT ID FROM wp_posts ORDER BY ID ASC LIMIT 100
20    ) AS first_100
21    UNION
22    SELECT ID FROM (
23        SELECT ID FROM wp_posts ORDER BY ID DESC LIMIT 100
24    ) AS last_100
25)
26ORDER BY ID
27LIMIT 20;
28
29-- Analyze post types in deletion scope
30SELECT post_type, COUNT(*) AS count
31FROM wp_posts
32WHERE ID NOT IN (
33    SELECT ID FROM (
34        SELECT ID FROM wp_posts ORDER BY ID ASC LIMIT 100
35    ) AS first_100
36    UNION
37    SELECT ID FROM (
38        SELECT ID FROM wp_posts ORDER BY ID DESC LIMIT 100
39    ) AS last_100
40)
41GROUP BY post_type;

Monitoring Deletion Progress

For long-running operations, monitor progress and resource usage:

sql

1-- Create progress monitoring table
2CREATE TEMPORARY TABLE deletion_progress (
3    checkpoint_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
4    posts_remaining INT,
5    posts_deleted INT,
6    execution_time_seconds INT
7);
8
9-- Monitor deletion in real-time (run in separate session)
10SELECT
11    p.checkpoint_time,
12    p.posts_remaining,
13    p.posts_deleted,
14    p.execution_time_seconds,
15    ROUND(p.posts_deleted / p.execution_time_seconds, 2) AS deletion_rate_per_sec
16FROM deletion_progress p
17ORDER BY p.checkpoint_time DESC
18LIMIT 10;
19
20-- Track table size changes during deletion
21SELECT
22    table_name,
23    ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb,
24    table_rows,
25    ROUND(data_length / table_rows, 2) AS avg_row_length
26FROM information_schema.tables
27WHERE table_schema = DATABASE()
28AND table_name = 'wp_posts';
29
30-- Monitor active connections and processes
31SHOW PROCESSLIST;
32
33-- Check for blocking queries
34SELECT
35    r.trx_id waiting_trx_id,
36    r.trx_mysql_thread_id waiting_thread,
37    r.trx_query waiting_query,
38    b.trx_id blocking_trx_id,
39    b.trx_mysql_thread_id blocking_thread,
40    b.trx_query blocking_query
41FROM information_schema.innodb_lock_waits w
42INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
43INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

Advanced Deletion Strategies

Deletion by Post Type and Status

Selective deletion targeting specific content types:

sql

1-- Delete only draft posts outside retention range
2DELETE FROM wp_posts
3WHERE post_status = 'draft'
4AND ID NOT IN (
5    SELECT ID FROM (
6        SELECT ID FROM wp_posts
7        WHERE post_status = 'draft'
8        ORDER BY ID ASC LIMIT 50
9    ) AS first_50
10    UNION
11    SELECT ID FROM (
12        SELECT ID FROM wp_posts
13        WHERE post_status = 'draft'
14        ORDER BY ID DESC LIMIT 50
15    ) AS last_50
16);
17
18-- Delete post revisions older than 30 days
19DELETE FROM wp_posts
20WHERE post_type = 'revision'
21AND post_modified < DATE_SUB(NOW(), INTERVAL 30 DAY);
22
23-- Delete auto-drafts
24DELETE FROM wp_posts
25WHERE post_status = 'auto-draft'
26AND post_date < DATE_SUB(NOW(), INTERVAL 7 DAY);

Safe Transaction-Wrapped Deletion

Use explicit transactions for rollback capability:

sql

1-- Start transaction
2START TRANSACTION;
3
4-- Store deletion scope for verification
5CREATE TEMPORARY TABLE deleted_posts_backup AS
6SELECT * FROM wp_posts
7WHERE ID NOT IN (
8    SELECT ID FROM (
9        SELECT ID FROM wp_posts ORDER BY ID ASC LIMIT 100
10    ) AS first_100
11    UNION
12    SELECT ID FROM (
13        SELECT ID FROM wp_posts ORDER BY ID DESC LIMIT 100
14    ) AS last_100
15);
16
17-- Verify temporary table
18SELECT COUNT(*) FROM deleted_posts_backup;
19
20-- Perform deletion
21DELETE FROM wp_posts
22WHERE ID IN (SELECT ID FROM deleted_posts_backup);
23
24-- Verify results - if satisfactory, commit; otherwise rollback
25SELECT
26    (SELECT COUNT(*) FROM wp_posts) AS remaining_posts,
27    (SELECT COUNT(*) FROM deleted_posts_backup) AS deleted_posts;
28
29-- COMMIT; -- Uncomment to finalize
30-- ROLLBACK; -- Uncomment to undo

Batch Deletion for Large Datasets

For databases with millions of posts, deleting in batches prevents long-running locks and replication lag:

sql

1-- Delete in batches of 1000 until complete
2SET @batch_size = 1000;
3SET @rows_affected = 1;
4
5WHILE @rows_affected > 0 DO
6    DELETE FROM wp_posts
7    WHERE ID NOT IN (
8        SELECT ID FROM (
9            SELECT ID FROM wp_posts ORDER BY ID ASC LIMIT 100
10        ) AS first_100
11        UNION
12        SELECT ID FROM (
13            SELECT ID FROM wp_posts ORDER BY ID DESC LIMIT 100
14        ) AS last_100
15    )
16    LIMIT @batch_size;
17
18    SET @rows_affected = ROW_COUNT();
19
20    -- Pause to allow replication catchup and reduce I/O pressure
21    SELECT SLEEP(0.5);
22END WHILE;

After post deletion, clean up orphaned records in related tables:

sql

1-- Remove orphaned post metadata
2DELETE pm FROM wp_postmeta pm
3LEFT JOIN wp_posts p ON pm.post_id = p.ID
4WHERE p.ID IS NULL;
5
6-- Remove orphaned term relationships
7DELETE tr FROM wp_term_relationships tr
8LEFT JOIN wp_posts p ON tr.object_id = p.ID
9WHERE p.ID IS NULL;
10
11-- Update comment counts for posts
12UPDATE wp_posts p
13SET comment_count = (
14    SELECT COUNT(*)
15    FROM wp_comments c
16    WHERE c.comment_post_ID = p.ID
17    AND c.comment_approved = '1'
18);
19
20-- Optional: Delete orphaned comments
21DELETE c FROM wp_comments c
22LEFT JOIN wp_posts p ON c.comment_post_ID = p.ID
23WHERE p.ID IS NULL;

Database Health and Statistics Analysis

After cleanup operations, perform comprehensive health checks:

sql

1-- Comprehensive table statistics
2SELECT
3    table_name,
4    engine,
5    table_rows,
6    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
7    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
8    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb,
9    ROUND(data_free / 1024 / 1024, 2) AS free_mb,
10    ROUND((data_free / (data_length + index_length + data_free)) * 100, 2) AS fragmentation_percent
11FROM information_schema.tables
12WHERE table_schema = DATABASE()
13AND table_name IN ('wp_posts', 'wp_postmeta', 'wp_comments', 'wp_term_relationships')
14ORDER BY (data_length + index_length) DESC;
15
16-- Index cardinality analysis
17SELECT
18    table_name,
19    index_name,
20    non_unique,
21    seq_in_index,
22    column_name,
23    cardinality,
24    CASE
25        WHEN cardinality IS NULL THEN 'N/A'
26        WHEN cardinality = 0 THEN 'Poor'
27        WHEN cardinality < 100 THEN 'Low'
28        WHEN cardinality < 1000 THEN 'Medium'
29        ELSE 'High'
30    END AS selectivity
31FROM information_schema.statistics
32WHERE table_schema = DATABASE()
33AND table_name = 'wp_posts'
34ORDER BY table_name, index_name, seq_in_index;
35
36-- Check for duplicate or redundant indexes
37SELECT
38    a.table_name,
39    a.index_name AS index_1,
40    GROUP_CONCAT(a.column_name ORDER BY a.seq_in_index) AS columns_1,
41    b.index_name AS index_2,
42    GROUP_CONCAT(b.column_name ORDER BY b.seq_in_index) AS columns_2
43FROM information_schema.statistics a
44JOIN information_schema.statistics b ON a.table_schema = b.table_schema
45    AND a.table_name = b.table_name
46    AND a.index_name != b.index_name
47    AND a.column_name = b.column_name
48    AND a.seq_in_index = b.seq_in_index
49WHERE a.table_schema = DATABASE()
50AND a.table_name = 'wp_posts'
51GROUP BY a.table_name, a.index_name, b.index_name
52HAVING columns_1 = columns_2;
53
54-- Identify orphaned metadata across all post-related tables
55SELECT
56    'wp_postmeta' AS table_name,
57    COUNT(*) AS orphaned_records,
58    ROUND(SUM(LENGTH(meta_value)) / 1024 / 1024, 2) AS wasted_mb
59FROM wp_postmeta pm
60LEFT JOIN wp_posts p ON pm.post_id = p.ID
61WHERE p.ID IS NULL
62
63UNION ALL
64
65SELECT
66    'wp_comments' AS table_name,
67    COUNT(*) AS orphaned_records,
68    ROUND(SUM(LENGTH(comment_content)) / 1024 / 1024, 2) AS wasted_mb
69FROM wp_comments c
70LEFT JOIN wp_posts p ON c.comment_post_ID = p.ID
71WHERE p.ID IS NULL
72
73UNION ALL
74
75SELECT
76    'wp_term_relationships' AS table_name,
77    COUNT(*) AS orphaned_records,
78    0 AS wasted_mb
79FROM wp_term_relationships tr
80LEFT JOIN wp_posts p ON tr.object_id = p.ID
81WHERE p.ID IS NULL;
82
83-- Analyze post type distribution and size
84SELECT
85    post_type,
86    post_status,
87    COUNT(*) AS post_count,
88    ROUND(AVG(LENGTH(post_content)), 2) AS avg_content_length,
89    ROUND(SUM(LENGTH(post_content)) / 1024 / 1024, 2) AS total_content_mb,
90    MIN(post_date) AS oldest_post,
91    MAX(post_date) AS newest_post
92FROM wp_posts
93GROUP BY post_type, post_status
94ORDER BY post_count DESC;

Deletion by Post Type and Status

Selective deletion targeting specific content types:

sql

1-- Delete only draft posts outside retention range
2DELETE FROM wp_posts
3WHERE post_status = 'draft'
4AND ID NOT IN (
5    SELECT ID FROM (
6        SELECT ID FROM wp_posts
7        WHERE post_status = 'draft'
8        ORDER BY ID ASC LIMIT 50
9    ) AS first_50
10    UNION
11    SELECT ID FROM (
12        SELECT ID FROM wp_posts
13        WHERE post_status = 'draft'
14        ORDER BY ID DESC LIMIT 50
15    ) AS last_50
16);
17
18-- Delete post revisions older than 30 days
19DELETE FROM wp_posts
20WHERE post_type = 'revision'
21AND post_modified < DATE_SUB(NOW(), INTERVAL 30 DAY);
22
23-- Delete auto-drafts
24DELETE FROM wp_posts
25WHERE post_status = 'auto-draft'
26AND post_date < DATE_SUB(NOW(), INTERVAL 7 DAY);

Safe Transaction-Wrapped Deletion

Use explicit transactions for rollback capability:

sql

1-- Start transaction
2START TRANSACTION;
3
4-- Store deletion scope for verification
5CREATE TEMPORARY TABLE deleted_posts_backup AS
6SELECT * FROM wp_posts
7WHERE ID NOT IN (
8    SELECT ID FROM (
9        SELECT ID FROM wp_posts ORDER BY ID ASC LIMIT 100
10    ) AS first_100
11    UNION
12    SELECT ID FROM (
13        SELECT ID FROM wp_posts ORDER BY ID DESC LIMIT 100
14    ) AS last_100
15);
16
17-- Verify temporary table
18SELECT COUNT(*) FROM deleted_posts_backup;
19
20-- Perform deletion
21DELETE FROM wp_posts
22WHERE ID IN (SELECT ID FROM deleted_posts_backup);
23
24-- Verify results - if satisfactory, commit; otherwise rollback
25SELECT
26    (SELECT COUNT(*) FROM wp_posts) AS remaining_posts,
27    (SELECT COUNT(*) FROM deleted_posts_backup) AS deleted_posts;
28
29-- COMMIT; -- Uncomment to finalize
30-- ROLLBACK; -- Uncomment to undo

Post-Deletion Optimization

Table Optimization and Defragmentation

After bulk deletions, reclaim disk space and rebuild indexes:

sql

1-- Optimize wp_posts table
2OPTIMIZE TABLE wp_posts;
3
4-- Analyze table statistics for query planner
5ANALYZE TABLE wp_posts;
6
7-- Check table fragmentation status
8SELECT
9    table_name,
10    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
11    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
12    ROUND(data_free / 1024 / 1024, 2) AS free_mb
13FROM information_schema.tables
14WHERE table_schema = DATABASE()
15AND table_name = 'wp_posts';

Reset Auto-Increment Counter

If all early posts are deleted, reset the auto-increment value:

sql

1-- Find the maximum ID
2SELECT MAX(ID) FROM wp_posts;
3
4-- Reset auto-increment to next available value
5ALTER TABLE wp_posts AUTO_INCREMENT = 1;
6
7-- Verify new auto-increment value
8SELECT AUTO_INCREMENT
9FROM information_schema.tables
10WHERE table_schema = DATABASE()
11AND table_name = 'wp_posts';

Index Optimization and Statistics

After bulk deletions, index statistics may become outdated, leading to suboptimal query plans:

sql

1-- Analyze table to update index statistics
2ANALYZE TABLE wp_posts;
3
4-- Check index usage statistics
5SELECT
6    OBJECT_SCHEMA AS database_name,
7    OBJECT_NAME AS table_name,
8    INDEX_NAME,
9    COUNT_STAR AS total_accesses,
10    COUNT_READ AS read_operations,
11    COUNT_WRITE AS write_operations,
12    COUNT_FETCH AS rows_fetched,
13    COUNT_INSERT AS rows_inserted,
14    COUNT_UPDATE AS rows_updated,
15    COUNT_DELETE AS rows_deleted
16FROM performance_schema.table_io_waits_summary_by_index_usage
17WHERE OBJECT_SCHEMA = DATABASE()
18AND OBJECT_NAME = 'wp_posts'
19ORDER BY COUNT_STAR DESC;
20
21-- Find unused indexes (candidates for removal)
22SELECT
23    t.TABLE_SCHEMA AS database_name,
24    t.TABLE_NAME AS table_name,
25    s.INDEX_NAME,
26    s.COLUMN_NAME,
27    s.SEQ_IN_INDEX AS column_position,
28    s.CARDINALITY,
29    ROUND((s.CARDINALITY / t.TABLE_ROWS) * 100, 2) AS selectivity_percent,
30    CASE
31        WHEN u.INDEX_NAME IS NULL THEN 'UNUSED - Consider dropping'
32        ELSE 'USED'
33    END AS usage_status
34FROM information_schema.STATISTICS s
35INNER JOIN information_schema.TABLES t
36    ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
37    AND s.TABLE_NAME = t.TABLE_NAME
38LEFT JOIN (
39    SELECT DISTINCT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME
40    FROM performance_schema.table_io_waits_summary_by_index_usage
41    WHERE COUNT_STAR > 0
42) u ON s.TABLE_SCHEMA = u.OBJECT_SCHEMA
43    AND s.TABLE_NAME = u.OBJECT_NAME
44    AND s.INDEX_NAME = u.INDEX_NAME
45WHERE s.TABLE_SCHEMA = DATABASE()
46AND s.TABLE_NAME = 'wp_posts'
47AND s.INDEX_NAME != 'PRIMARY'
48ORDER BY t.TABLE_NAME, s.INDEX_NAME, s.SEQ_IN_INDEX;
49
50-- Check for duplicate indexes
51SELECT
52    a.TABLE_NAME,
53    a.INDEX_NAME AS index1,
54    GROUP_CONCAT(a.COLUMN_NAME ORDER BY a.SEQ_IN_INDEX) AS columns1,
55    b.INDEX_NAME AS index2,
56    GROUP_CONCAT(b.COLUMN_NAME ORDER BY b.SEQ_IN_INDEX) AS columns2,
57    'Potential duplicate - review for removal' AS recommendation
58FROM information_schema.STATISTICS a
59JOIN information_schema.STATISTICS b
60    ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
61    AND a.TABLE_NAME = b.TABLE_NAME
62    AND a.INDEX_NAME < b.INDEX_NAME
63WHERE a.TABLE_SCHEMA = DATABASE()
64AND a.TABLE_NAME = 'wp_posts'
65GROUP BY a.TABLE_NAME, a.INDEX_NAME, b.INDEX_NAME
66HAVING columns1 = columns2;
67
68-- Rebuild indexes to reclaim space and improve performance
69ALTER TABLE wp_posts ENGINE=InnoDB;
70
71-- Or rebuild specific index
72ALTER TABLE wp_posts DROP INDEX type_status_date;
73ALTER TABLE wp_posts ADD INDEX type_status_date (post_type, post_status, post_date, ID);
74
75-- Force index hints for queries
76-- Use when optimizer makes poor choices after deletion
77SELECT * FROM wp_posts FORCE INDEX (type_status_date)
78WHERE post_type = 'post'
79AND post_status = 'publish'
80ORDER BY post_date DESC
81LIMIT 10;

Index Best Practices for WordPress:

Composite Index Ordering: Most selective columns first

sql

1-- Good: Filters reduce dataset quickly
2INDEX (post_type, post_status, post_date)
3
4-- Bad: Less selective column first
5INDEX (post_date, post_type, post_status)

Covering Indexes: Include all columns in SELECT

sql

1-- Query: SELECT post_title, post_date FROM wp_posts WHERE post_type = 'post'
2-- Covering index includes all needed columns
3CREATE INDEX idx_covering ON wp_posts(post_type, post_date, post_title);

Prefix Indexes: For long VARCHAR/TEXT columns

sql

1-- Only index first 50 characters of post_name
2CREATE INDEX idx_post_name_prefix ON wp_posts(post_name(50));

Performance Monitoring

Query Execution Analysis

Monitor query performance with EXPLAIN and execution profiling:

sql

1-- Basic EXPLAIN for deletion query
2EXPLAIN DELETE FROM wp_posts
3WHERE ID NOT IN (
4    SELECT ID FROM (
5        SELECT ID FROM wp_posts ORDER BY ID ASC LIMIT 100
6    ) AS first_100
7    UNION
8    SELECT ID FROM (
9        SELECT ID FROM wp_posts ORDER BY ID DESC LIMIT 100
10    ) AS last_100
11);
12
13-- Extended EXPLAIN with execution plan details
14EXPLAIN FORMAT=JSON
15DELETE FROM wp_posts
16WHERE ID NOT IN (
17    SELECT ID FROM (
18        SELECT ID FROM wp_posts ORDER BY ID ASC LIMIT 100
19    ) AS first_100
20    UNION
21    SELECT ID FROM (
22        SELECT ID FROM wp_posts ORDER BY ID DESC LIMIT 100
23    ) AS last_100
24);
25
26-- Enable query profiling
27SET profiling = 1;
28
29-- Run your deletion query
30-- DELETE FROM wp_posts WHERE [condition];
31
32-- View profiling results
33SHOW PROFILES;
34
35-- Get detailed timing for specific query
36SHOW PROFILE FOR QUERY 1;
37
38-- View CPU and block I/O statistics
39SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;
40
41-- Disable profiling when done
42SET profiling = 0;
43
44-- Analyze slow query log patterns
45SELECT
46    DIGEST_TEXT,
47    COUNT_STAR AS execution_count,
48    ROUND(AVG_TIMER_WAIT / 1000000000000, 2) AS avg_time_sec,
49    ROUND(MAX_TIMER_WAIT / 1000000000000, 2) AS max_time_sec,
50    ROUND(SUM_ROWS_EXAMINED / COUNT_STAR, 0) AS avg_rows_examined,
51    ROUND(SUM_ROWS_SENT / COUNT_STAR, 0) AS avg_rows_sent
52FROM performance_schema.events_statements_summary_by_digest
53WHERE DIGEST_TEXT LIKE '%wp_posts%'
54ORDER BY SUM_TIMER_WAIT DESC
55LIMIT 10;

Understanding EXPLAIN Output:

Column	Description	Optimization Goal
`select_type`	Type of SELECT (SIMPLE, PRIMARY, SUBQUERY)	Minimize subqueries when possible
`type`	Join type (ALL, index, range, ref, eq_ref, const)	Aim for ref or better (avoid ALL)
`possible_keys`	Indexes that could be used	Ensure relevant indexes exist
`key`	Actual index used	Verify correct index selection
`rows`	Estimated rows examined	Lower is better
`Extra`	Additional execution details	Watch for "Using filesort" or "Using temporary"

Disk Space Verification

Check actual disk space reclaimed:

bash

1# Before deletion
2du -sh /var/lib/mysql/database_name/
3
4# After deletion and OPTIMIZE
5du -sh /var/lib/mysql/database_name/
6
7# Check specific table files
8ls -lh /var/lib/mysql/database_name/wp_posts.*

Troubleshooting

Common Issues and Solutions

Issue: Query times out on large databases

Solution: Use batch deletion with LIMIT and iterate:

sql

1-- Incremental deletion
2SET @deleted = 1;
3WHILE @deleted > 0 DO
4    DELETE FROM wp_posts
5    WHERE ID NOT IN (
6        SELECT ID FROM (
7            SELECT ID FROM wp_posts ORDER BY ID ASC LIMIT 100
8        ) AS first_100
9        UNION
10        SELECT ID FROM (
11            SELECT ID FROM wp_posts ORDER BY ID DESC LIMIT 100
12        ) AS last_100
13    )
14    LIMIT 5000;
15    SET @deleted = ROW_COUNT();
16    SELECT @deleted AS 'Rows deleted in this batch';
17END WHILE;

Issue: Replication lag in master-slave setup

Solution: Add delays between batch operations:

sql

1DELETE FROM wp_posts WHERE [...] LIMIT 1000;
2SELECT SLEEP(2); -- 2 second pause

Issue: Foreign key constraints prevent deletion

Solution: Temporarily disable foreign key checks (use with extreme caution):

sql

1SET FOREIGN_KEY_CHECKS = 0;
2-- Perform deletion
3DELETE FROM wp_posts WHERE [...];
4SET FOREIGN_KEY_CHECKS = 1;

Best Practices Summary

Always backup: Create full database dumps before any DELETE operation
Test in staging: Validate queries on non-production data first
Use transactions: Wrap operations in START TRANSACTION for rollback capability
Monitor replication: Check slave lag in replicated environments
Schedule maintenance: Perform bulk deletions during low-traffic periods
Verify results: Run count queries before and after operations
Clean related data: Remove orphaned metadata and relationships
Optimize tables: Run OPTIMIZE TABLE after bulk deletions
Update statistics: Use ANALYZE TABLE to refresh query optimizer statistics
Document changes: Log what was deleted and why for audit trails
Consider alternatives: Evaluate WordPress plugins like WP-Optimize or WP-Sweep for safer GUI-based cleanup

WordPress-Specific Optimizations

Using WP-CLI for Database Management

WordPress Command Line Interface (WP-CLI) provides safer abstraction layer for database operations:

bash

1# Check database status
2wp db check
3
4# Optimize database tables
5wp db optimize
6
7# Export database with compression
8wp db export wordpress_backup.sql.gz
9
10# Search and replace in database (useful after cleanup)
11wp search-replace 'old-url.com' 'new-url.com' --dry-run
12
13# Clean post revisions
14wp post delete $(wp post list --post_type='revision' --format=ids) --force
15
16# Delete auto-drafts
17wp post delete $(wp post list --post_status=auto-draft --format=ids) --force
18
19# List orphaned postmeta
20wp db query "SELECT meta_id, post_id FROM wp_postmeta pm
21             LEFT JOIN wp_posts p ON pm.post_id = p.ID
22             WHERE p.ID IS NULL LIMIT 10;"
23
24# Delete orphaned postmeta
25wp db query "DELETE pm FROM wp_postmeta pm
26             LEFT JOIN wp_posts p ON pm.post_id = p.ID
27             WHERE p.ID IS NULL;"

WordPress PHP Functions for Safe Database Operations

When building custom WordPress plugins or themes that handle post deletion:

php

1<?php
2/**
3 * Safe bulk post deletion using WordPress APIs
4 * Respects post relationships and triggers proper hooks
5 */
6function safely_delete_old_posts($keep_first = 100, $keep_last = 100) {
7    global $wpdb;
8
9    // Get total post count
10    $total = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = 'post'");
11
12    if ($total <= ($keep_first + $keep_last)) {
13        return new WP_Error('insufficient_posts', 'Not enough posts to delete');
14    }
15
16    // Get IDs to keep
17    $keep_ids = array_merge(
18        $wpdb->get_col($wpdb->prepare(
19            "SELECT ID FROM {$wpdb->posts}
20             WHERE post_type = 'post'
21             ORDER BY ID ASC LIMIT %d",
22            $keep_first
23        )),
24        $wpdb->get_col($wpdb->prepare(
25            "SELECT ID FROM {$wpdb->posts}
26             WHERE post_type = 'post'
27             ORDER BY ID DESC LIMIT %d",
28            $keep_last
29        ))
30    );
31
32    // Get posts to delete
33    $delete_ids = $wpdb->get_col($wpdb->prepare(
34        "SELECT ID FROM {$wpdb->posts}
35         WHERE post_type = 'post'
36         AND ID NOT IN (" . implode(',', array_map('intval', $keep_ids)) . ")"
37    ));
38
39    $deleted_count = 0;
40
41    // Delete in batches of 50
42    foreach (array_chunk($delete_ids, 50) as $batch) {
43        foreach ($batch as $post_id) {
44            // wp_delete_post handles metadata, comments, and relationships
45            if (wp_delete_post($post_id, true)) {
46                $deleted_count++;
47            }
48        }
49
50        // Prevent memory exhaustion
51        wp_cache_flush();
52
53        // Give server breathing room
54        sleep(1);
55    }
56
57    return $deleted_count;
58}
59
60/**
61 * Clean orphaned postmeta using WordPress database class
62 */
63function clean_orphaned_postmeta() {
64    global $wpdb;
65
66    $deleted = $wpdb->query("
67        DELETE pm FROM {$wpdb->postmeta} pm
68        LEFT JOIN {$wpdb->posts} p ON pm.post_id = p.ID
69        WHERE p.ID IS NULL
70    ");
71
72    return $deleted;
73}
74
75/**
76 * Get database statistics
77 */
78function get_wordpress_db_stats() {
79    global $wpdb;
80
81    $stats = array();
82
83    // Post count by type
84    $stats['posts_by_type'] = $wpdb->get_results("
85        SELECT post_type, post_status, COUNT(*) as count
86        FROM {$wpdb->posts}
87        GROUP BY post_type, post_status
88        ORDER BY count DESC
89    ");
90
91    // Orphaned postmeta
92    $stats['orphaned_postmeta'] = $wpdb->get_var("
93        SELECT COUNT(*) FROM {$wpdb->postmeta} pm
94        LEFT JOIN {$wpdb->posts} p ON pm.post_id = p.ID
95        WHERE p.ID IS NULL
96    ");
97
98    // Orphaned comments
99    $stats['orphaned_comments'] = $wpdb->get_var("
100        SELECT COUNT(*) FROM {$wpdb->comments} c
101        LEFT JOIN {$wpdb->posts} p ON c.comment_post_ID = p.ID
102        WHERE p.ID IS NULL
103    ");
104
105    // Table sizes
106    $stats['table_sizes'] = $wpdb->get_results("
107        SELECT
108            table_name,
109            ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
110        FROM information_schema.tables
111        WHERE table_schema = '" . DB_NAME . "'
112        AND table_name LIKE '{$wpdb->prefix}%'
113        ORDER BY (data_length + index_length) DESC
114    ");
115
116    return $stats;
117}
118
119/**
120 * Schedule automated cleanup as WordPress cron job
121 */
122function schedule_database_cleanup() {
123    if (!wp_next_scheduled('custom_db_cleanup')) {
124        wp_schedule_event(time(), 'weekly', 'custom_db_cleanup');
125    }
126}
127add_action('wp', 'schedule_database_cleanup');
128
129function perform_scheduled_cleanup() {
130    global $wpdb;
131
132    // Delete old revisions
133    $wpdb->query("
134        DELETE FROM {$wpdb->posts}
135        WHERE post_type = 'revision'
136        AND post_modified < DATE_SUB(NOW(), INTERVAL 30 DAY)
137        LIMIT 1000
138    ");
139
140    // Delete old auto-drafts
141    $wpdb->query("
142        DELETE FROM {$wpdb->posts}
143        WHERE post_status = 'auto-draft'
144        AND post_date < DATE_SUB(NOW(), INTERVAL 7 DAY)
145        LIMIT 1000
146    ");
147
148    // Clean orphaned metadata
149    clean_orphaned_postmeta();
150}
151add_action('custom_db_cleanup', 'perform_scheduled_cleanup');
152?>

WordPress Database Table Relationships

Understanding how WordPress tables interconnect helps prevent data integrity issues:

sql

1-- View all WordPress tables and their sizes
2SELECT
3    table_name,
4    engine,
5    table_rows,
6    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_size_mb,
7    ROUND(data_length / 1024 / 1024, 2) AS data_size_mb,
8    ROUND(index_length / 1024 / 1024, 2) AS index_size_mb
9FROM information_schema.tables
10WHERE table_schema = DATABASE()
11AND table_name LIKE 'wp_%'
12ORDER BY (data_length + index_length) DESC;
13
14-- Check referential integrity across post-related tables
15SELECT
16    'Posts without authors' AS issue,
17    COUNT(*) AS count
18FROM wp_posts p
19LEFT JOIN wp_users u ON p.post_author = u.ID
20WHERE u.ID IS NULL AND p.post_author != 0
21
22UNION ALL
23
24SELECT
25    'Comments on deleted posts' AS issue,
26    COUNT(*) AS count
27FROM wp_comments c
28LEFT JOIN wp_posts p ON c.comment_post_ID = p.ID
29WHERE p.ID IS NULL
30
31UNION ALL
32
33SELECT
34    'Postmeta for deleted posts' AS issue,
35    COUNT(*) AS count
36FROM wp_postmeta pm
37LEFT JOIN wp_posts p ON pm.post_id = p.ID
38WHERE p.ID IS NULL
39
40UNION ALL
41
42SELECT
43    'Term relationships for deleted posts' AS issue,
44    COUNT(*) AS count
45FROM wp_term_relationships tr
46LEFT JOIN wp_posts p ON tr.object_id = p.ID
47WHERE p.ID IS NULL AND tr.term_taxonomy_id IN (
48    SELECT term_taxonomy_id
49    FROM wp_term_taxonomy
50    WHERE taxonomy IN ('category', 'post_tag')
51);

Performance Impact Analysis

Before and after metrics to measure optimization impact:

sql

1-- Before optimization: Capture baseline metrics
2CREATE TABLE optimization_baseline AS
3SELECT
4    'wp_posts' AS table_name,
5    COUNT(*) AS row_count,
6    ROUND(AVG(LENGTH(post_content))) AS avg_content_size,
7    MIN(post_date) AS oldest_date,
8    MAX(post_date) AS newest_date,
9    (SELECT COUNT(*) FROM wp_postmeta) AS postmeta_count,
10    (SELECT COUNT(*) FROM wp_comments) AS comments_count,
11    NOW() AS measured_at
12FROM wp_posts;
13
14-- After optimization: Compare results
15SELECT
16    b.table_name,
17    b.row_count AS before_rows,
18    (SELECT COUNT(*) FROM wp_posts) AS after_rows,
19    b.row_count - (SELECT COUNT(*) FROM wp_posts) AS rows_deleted,
20    b.postmeta_count AS before_postmeta,
21    (SELECT COUNT(*) FROM wp_postmeta) AS after_postmeta,
22    b.comments_count AS before_comments,
23    (SELECT COUNT(*) FROM wp_comments) AS after_comments,
24    TIMESTAMPDIFF(MINUTE, b.measured_at, NOW()) AS minutes_elapsed
25FROM optimization_baseline b;
26
27-- Query performance comparison
28-- Run this BEFORE optimization
29EXPLAIN ANALYZE
30SELECT * FROM wp_posts
31WHERE post_type = 'post'
32AND post_status = 'publish'
33ORDER BY post_date DESC
34LIMIT 10;
35
36-- Run the same query AFTER optimization to compare execution time

Monitoring and Alerting

Real-Time Performance Monitoring

Set up comprehensive monitoring for database operations:

sql

1-- Monitor long-running queries
2SELECT
3    ID,
4    USER,
5    HOST,
6    DB,
7    COMMAND,
8    TIME AS duration_seconds,
9    STATE,
10    INFO AS query_text,
11    TIME_MS / 1000 AS duration_decimal_sec
12FROM information_schema.PROCESSLIST
13WHERE COMMAND != 'Sleep'
14AND TIME > 10  -- Queries running longer than 10 seconds
15ORDER BY TIME DESC;
16
17-- Monitor table lock contention
18SELECT
19    OBJECT_SCHEMA AS database_name,
20    OBJECT_NAME AS table_name,
21    COUNT_STAR AS total_waits,
22    SUM_TIMER_WAIT / 1000000000000 AS total_wait_time_sec,
23    AVG_TIMER_WAIT / 1000000000000 AS avg_wait_time_sec,
24    MAX_TIMER_WAIT / 1000000000000 AS max_wait_time_sec
25FROM performance_schema.table_lock_waits_summary_by_table
26WHERE OBJECT_SCHEMA = DATABASE()
27AND OBJECT_NAME = 'wp_posts'
28ORDER BY SUM_TIMER_WAIT DESC;
29
30-- Track disk I/O per table
31SELECT
32    OBJECT_SCHEMA,
33    OBJECT_NAME,
34    COUNT_READ AS read_operations,
35    COUNT_WRITE AS write_operations,
36    COUNT_FETCH AS rows_fetched,
37    COUNT_INSERT AS rows_inserted,
38    COUNT_UPDATE AS rows_updated,
39    COUNT_DELETE AS rows_deleted,
40    SUM_TIMER_WAIT / 1000000000000 AS total_io_time_sec
41FROM performance_schema.table_io_waits_summary_by_table
42WHERE OBJECT_SCHEMA = DATABASE()
43AND OBJECT_NAME IN ('wp_posts', 'wp_postmeta', 'wp_comments')
44ORDER BY SUM_TIMER_WAIT DESC;
45
46-- Monitor InnoDB row operations
47SHOW GLOBAL STATUS LIKE 'Innodb_rows%';
48
49-- Check for table cache pressure
50SHOW GLOBAL STATUS LIKE 'Open%tables';
51SHOW VARIABLES LIKE 'table_open_cache';
52
53-- Monitor connection usage
54SHOW STATUS LIKE 'Threads_connected';
55SHOW STATUS LIKE 'Max_used_connections';
56SHOW VARIABLES LIKE 'max_connections';

Automated Health Checks

Create stored procedures for recurring health checks:

sql

1DELIMITER $$
2
3CREATE PROCEDURE check_database_health()
4BEGIN
5    -- Check for orphaned records
6    SELECT
7        'Orphaned postmeta' AS check_type,
8        COUNT(*) AS issues_found,
9        CASE
10            WHEN COUNT(*) = 0 THEN 'PASS'
11            WHEN COUNT(*) < 100 THEN 'WARNING'
12            ELSE 'CRITICAL'
13        END AS status
14    FROM wp_postmeta pm
15    LEFT JOIN wp_posts p ON pm.post_id = p.ID
16    WHERE p.ID IS NULL
17
18    UNION ALL
19
20    -- Check table fragmentation
21    SELECT
22        'Table fragmentation' AS check_type,
23        ROUND((data_free / (data_length + index_length + data_free)) * 100, 2) AS issues_found,
24        CASE
25            WHEN (data_free / (data_length + index_length + data_free)) * 100 < 10 THEN 'PASS'
26            WHEN (data_free / (data_length + index_length + data_free)) * 100 < 25 THEN 'WARNING'
27            ELSE 'CRITICAL'
28        END AS status
29    FROM information_schema.tables
30    WHERE table_schema = DATABASE()
31    AND table_name = 'wp_posts'
32
33    UNION ALL
34
35    -- Check InnoDB buffer pool usage
36    SELECT
37        'Buffer pool usage' AS check_type,
38        ROUND(
39            (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
40             WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data') /
41            (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
42             WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total') * 100,
43            2
44        ) AS issues_found,
45        CASE
46            WHEN (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
47                  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data') /
48                 (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
49                  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total') * 100 < 80 THEN 'PASS'
50            WHEN (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
51                  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data') /
52                 (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
53                  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total') * 100 < 95 THEN 'WARNING'
54            ELSE 'CRITICAL'
55        END AS status;
56END$$
57
58DELIMITER ;
59
60-- Run health check
61CALL check_database_health();

Logging and Audit Trail

Implement comprehensive logging for deletion operations:

sql

1-- Create audit log table
2CREATE TABLE IF NOT EXISTS wp_deletion_audit (
3    id BIGINT AUTO_INCREMENT PRIMARY KEY,
4    deletion_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
5    deleted_by VARCHAR(100),
6    deletion_type VARCHAR(50),
7    records_deleted INT,
8    table_affected VARCHAR(100),
9    deletion_criteria TEXT,
10    execution_time_seconds DECIMAL(10,2),
11    disk_space_freed_mb DECIMAL(10,2),
12    notes TEXT,
13    INDEX idx_deletion_date (deletion_date),
14    INDEX idx_table_affected (table_affected)
15) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
16
17-- Log deletion operation
18INSERT INTO wp_deletion_audit (
19    deleted_by,
20    deletion_type,
21    records_deleted,
22    table_affected,
23    deletion_criteria,
24    execution_time_seconds,
25    disk_space_freed_mb,
26    notes
27) VALUES (
28    USER(),
29    'bulk_post_deletion',
30    (SELECT COUNT(*) FROM deleted_posts_backup),
31    'wp_posts',
32    'Kept first 100 and last 100 rows',
33    TIMESTAMPDIFF(SECOND, @start_time, NOW()),
34    (SELECT ROUND(data_free / 1024 / 1024, 2)
35     FROM information_schema.tables
36     WHERE table_schema = DATABASE() AND table_name = 'wp_posts'),
37    'Part of monthly cleanup routine'
38);
39
40-- Query audit history
41SELECT
42    deletion_date,
43    deleted_by,
44    deletion_type,
45    records_deleted,
46    table_affected,
47    execution_time_seconds,
48    disk_space_freed_mb,
49    notes
50FROM wp_deletion_audit
51ORDER BY deletion_date DESC
52LIMIT 20;
53
54-- Analyze deletion patterns
55SELECT
56    DATE_FORMAT(deletion_date, '%Y-%m') AS month,
57    SUM(records_deleted) AS total_deleted,
58    SUM(disk_space_freed_mb) AS total_space_freed_mb,
59    AVG(execution_time_seconds) AS avg_execution_time,
60    COUNT(*) AS deletion_operations
61FROM wp_deletion_audit
62GROUP BY DATE_FORMAT(deletion_date, '%Y-%m')
63ORDER BY month DESC;

Performance Baseline Documentation

Create a reference document of expected performance metrics:

bash

1# Create performance baseline script
2cat > /path/to/scripts/wp_performance_baseline.sh << 'EOF'
3#!/bin/bash
4
5# WordPress Database Performance Baseline
6# Run this before and after major database operations
7
8MYSQL_USER="your_user"
9MYSQL_PASS="your_pass"
10MYSQL_DB="your_database"
11OUTPUT_FILE="wp_perf_baseline_$(date +%Y%m%d_%H%M%S).txt"
12
13echo "=== WordPress Database Performance Baseline ===" > "$OUTPUT_FILE"
14echo "Generated: $(date)" >> "$OUTPUT_FILE"
15echo "" >> "$OUTPUT_FILE"
16
17# Table sizes
18echo "=== Table Sizes ===" >> "$OUTPUT_FILE"
19mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" "$MYSQL_DB" -e "
20SELECT
21    table_name,
22    table_rows,
23    ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
24FROM information_schema.tables
25WHERE table_schema = DATABASE()
26AND table_name LIKE 'wp_%'
27ORDER BY (data_length + index_length) DESC;
28" >> "$OUTPUT_FILE"
29
30# Post counts by type
31echo "" >> "$OUTPUT_FILE"
32echo "=== Post Counts by Type ===" >> "$OUTPUT_FILE"
33mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" "$MYSQL_DB" -e "
34SELECT post_type, post_status, COUNT(*) as count
35FROM wp_posts
36GROUP BY post_type, post_status
37ORDER BY count DESC;
38" >> "$OUTPUT_FILE"
39
40# Index statistics
41echo "" >> "$OUTPUT_FILE"
42echo "=== Index Cardinality ===" >> "$OUTPUT_FILE"
43mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" "$MYSQL_DB" -e "
44SELECT table_name, index_name, cardinality
45FROM information_schema.statistics
46WHERE table_schema = DATABASE()
47AND table_name = 'wp_posts'
48ORDER BY table_name, index_name;
49" >> "$OUTPUT_FILE"
50
51# Slow query statistics
52echo "" >> "$OUTPUT_FILE"
53echo "=== Top 10 Slowest Queries ===" >> "$OUTPUT_FILE"
54mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" "$MYSQL_DB" -e "
55SELECT
56    DIGEST_TEXT,
57    COUNT_STAR as exec_count,
58    ROUND(AVG_TIMER_WAIT / 1000000000000, 2) as avg_time_sec
59FROM performance_schema.events_statements_summary_by_digest
60WHERE SCHEMA_NAME = DATABASE()
61ORDER BY AVG_TIMER_WAIT DESC
62LIMIT 10;
63" >> "$OUTPUT_FILE"
64
65echo "" >> "$OUTPUT_FILE"
66echo "Baseline saved to: $OUTPUT_FILE"
67EOF
68
69chmod +x /path/to/scripts/wp_performance_baseline.sh

Troubleshooting Common Issues

Issue: Lock Wait Timeout Exceeded

Symptoms:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Solutions:

sql

1-- Increase lock wait timeout temporarily
2SET SESSION innodb_lock_wait_timeout = 300;
3
4-- Check what's holding locks
5SELECT
6    r.trx_id waiting_trx_id,
7    r.trx_mysql_thread_id waiting_thread,
8    r.trx_query waiting_query,
9    b.trx_id blocking_trx_id,
10    b.trx_mysql_thread_id blocking_thread,
11    b.trx_query blocking_query,
12    TIMESTAMPDIFF(SECOND, r.trx_started, NOW()) AS wait_age_seconds
13FROM information_schema.innodb_lock_waits w
14INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
15INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
16
17-- Kill blocking thread (use with caution)
18-- KILL <blocking_thread_id>;

Issue: Out of Memory Errors

Symptoms:

ERROR 1038 (HY001): Out of sort memory
ERROR 2006 (HY000): MySQL server has gone away

Solutions:

sql

1-- Increase memory buffers
2SET SESSION sort_buffer_size = 4194304;
3SET SESSION read_buffer_size = 2097152;
4
5-- Monitor memory usage
6SELECT
7    SUBSTRING_INDEX(event_name, '/', 2) AS memory_category,
8    ROUND(SUM(CURRENT_NUMBER_OF_BYTES_USED) / 1024 / 1024, 2) AS memory_used_mb
9FROM performance_schema.memory_summary_global_by_event_name
10WHERE event_name LIKE 'memory/%'
11GROUP BY memory_category
12ORDER BY SUM(CURRENT_NUMBER_OF_BYTES_USED) DESC
13LIMIT 10;

Issue: Disk Space Not Reclaimed

Symptoms: Table size doesn't decrease after deletion

Solutions:

sql

1-- Check fragmentation
2SELECT
3    table_name,
4    ROUND(data_free / 1024 / 1024, 2) AS free_space_mb,
5    ROUND((data_free / (data_length + index_length + data_free)) * 100, 2) AS fragmentation_percent
6FROM information_schema.tables
7WHERE table_schema = DATABASE()
8AND table_name = 'wp_posts';
9
10-- Reclaim space
11OPTIMIZE TABLE wp_posts;
12
13-- Verify file-per-table mode
14SHOW VARIABLES LIKE 'innodb_file_per_table';

Delete posts in WordPress DB to light up the database

Overview

Why Database Optimization Matters

Prerequisites

Important Considerations

Data Integrity

Understanding WordPress Database Schema

InnoDB Storage Engine Characteristics

MySQL Configuration for Bulk Operations

Backup Strategy

Safe Deletion: Retain First 100 and Last 100 Posts

Understanding the Query Mechanics

Pre-Deletion Validation Queries

Monitoring Deletion Progress

Advanced Deletion Strategies

Deletion by Post Type and Status

Safe Transaction-Wrapped Deletion

Batch Deletion for Large Datasets

Cascading Cleanup of Related Data

Database Health and Statistics Analysis

Deletion by Post Type and Status

Safe Transaction-Wrapped Deletion

Post-Deletion Optimization

Table Optimization and Defragmentation

Reset Auto-Increment Counter

Index Optimization and Statistics

Performance Monitoring

Query Execution Analysis

Disk Space Verification

Troubleshooting

Common Issues and Solutions

Best Practices Summary

WordPress-Specific Optimizations

Using WP-CLI for Database Management

WordPress PHP Functions for Safe Database Operations

WordPress Database Table Relationships

Performance Impact Analysis

Monitoring and Alerting

Real-Time Performance Monitoring

Automated Health Checks

Logging and Audit Trail

Performance Baseline Documentation

Troubleshooting Common Issues

Issue: Lock Wait Timeout Exceeded

Issue: Out of Memory Errors

Issue: Disk Space Not Reclaimed

Best Practices Summary