📊 WordPress 6.9 Database Optimization: Speed Up MySQL with PHP 8.3

Optimize WP_Query

// Bad - loads all fields
$query = new WP_Query([
    'posts_per_page' => 10,
]);

// Good - only needed fields
$query = new WP_Query([
    'posts_per_page' => 10,
    'fields' => 'ids',  // Only get IDs
    'no_found_rows' => true,  // Skip pagination count
    'update_post_meta_cache' => false,  // Skip meta cache
    'update_post_term_cache' => false,  // Skip term cache
]);

// Even better - custom SQL for complex queries
global $wpdb;
$results = $wpdb->get_results("
    SELECT ID, post_title, post_date
    FROM {$wpdb->posts}
    WHERE post_status = 'publish'
    AND post_type = 'post'
    ORDER BY post_date DESC
    LIMIT 10
");

Use prepare() for SQL Queries

// Safe and optimized
global $wpdb;
$post_type = 'post';
$status = 'publish';
$limit = 10;

$results = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT ID, post_title 
        FROM {$wpdb->posts} 
        WHERE post_type = %s 
        AND post_status = %s 
        ORDER BY post_date DESC 
        LIMIT %d",
        $post_type,
        $status,
        $limit
    )
);

Optimize Meta Queries

// Bad - multiple meta queries
$query = new WP_Query([
    'meta_query' => [
        ['key' => 'price', 'value' => 100, 'compare' => '>'],
        ['key' => 'stock', 'value' => 0, 'compare' => '>'],
    ]
]);

// Better - use EXISTS for better performance
$query = new WP_Query([
    'meta_query' => [
        'relation' => 'AND',
        ['key' => 'price', 'value' => 100, 'type' => 'NUMERIC', 'compare' => '>'],
        ['key' => 'stock', 'compare' => 'EXISTS'],
    ]
]);

// Best - custom SQL with JOINs
global $wpdb;
$results = $wpdb->get_results("
    SELECT p.ID, p.post_title
    FROM {$wpdb->posts} p
    INNER JOIN {$wpdb->postmeta} pm1 ON p.ID = pm1.post_id AND pm1.meta_key = 'price'
    INNER JOIN {$wpdb->postmeta} pm2 ON p.ID = pm2.post_id AND pm2.meta_key = 'stock'
    WHERE p.post_type = 'product'
    AND p.post_status = 'publish'
    AND pm1.meta_value + 0 > 100
    AND pm2.meta_value + 0 > 0
");

Use EXISTS Instead of IN for Subqueries

-- Slow - IN with subquery
SELECT ID, post_title 
FROM wp_posts 
WHERE ID IN (
    SELECT post_id 
    FROM wp_postmeta 
    WHERE meta_key = 'featured' 
    AND meta_value = 'yes'
);

-- Fast - EXISTS
SELECT p.ID, p.post_title 
FROM wp_posts p
WHERE EXISTS (
    SELECT 1 
    FROM wp_postmeta pm 
    WHERE pm.post_id = p.ID 
    AND pm.meta_key = 'featured' 
    AND pm.meta_value = 'yes'
);

Batch Process Large Operations

// Bad - updates all at once
$posts = get_posts(['posts_per_page' => -1]);
foreach ($posts as $post) {
    update_post_meta($post->ID, 'processed', true);
}

// Good - batch processing
$batch_size = 100;
$offset = 0;

while (true) {
    $posts = get_posts([
        'posts_per_page' => $batch_size,
        'offset' => $offset,
        'fields' => 'ids',
    ]);
    
    if (empty($posts)) break;
    
    global $wpdb;
    
    // Single query for batch update
    $wpdb->query(
        $wpdb->prepare(
            "INSERT INTO {$wpdb->postmeta} (post_id, meta_key, meta_value) 
            VALUES " . implode(',', array_fill(0, count($posts), '(%d, %s, %s)')) . "
            ON DUPLICATE KEY UPDATE meta_value = VALUES(meta_value)",
            array_merge(...array_map(function($id) {
                return [$id, 'processed', '1'];
            }, $posts))
        )
    );
    
    $offset += $batch_size;
    
    // Sleep to avoid overloading
    if (function_exists('usleep')) {
        usleep(500000); // 0.5 seconds
    }
}

Use Window Functions

-- Get posts with running total of comments
SELECT 
    p.ID,
    p.post_title,
    COUNT(c.comment_ID) as comment_count,
    SUM(COUNT(c.comment_ID)) OVER (
        ORDER BY p.post_date
        ROWS UNBOUNDED PRECEDING
    ) as running_total
FROM wp_posts p
LEFT JOIN wp_comments c ON p.ID = c.comment_post_ID
WHERE p.post_status = 'publish'
GROUP BY p.ID, p.post_title
ORDER BY p.post_date;

-- Rank posts by comments
SELECT 
    p.ID,
    p.post_title,
    COUNT(c.comment_ID) as comment_count,
    RANK() OVER (ORDER BY COUNT(c.comment_ID) DESC) as rank
FROM wp_posts p
LEFT JOIN wp_comments c ON p.ID = c.comment_post_ID
WHERE p.post_status = 'publish'
GROUP BY p.ID, p.post_title
HAVING comment_count > 0
ORDER BY rank
LIMIT 10;

Common Table Expressions (CTE)

-- Find posts with related content
WITH popular_posts AS (
    SELECT ID, post_title, post_date
    FROM wp_posts
    WHERE comment_count > 100
    AND post_status = 'publish'
),
recent_posts AS (
    SELECT ID, post_title, post_date
    FROM wp_posts
    WHERE post_date > DATE_SUB(NOW(), INTERVAL 7 DAY)
    AND post_status = 'publish'
)
SELECT 
    COALESCE(pp.ID, rp.ID) as ID,
    COALESCE(pp.post_title, rp.post_title) as post_title,
    CASE 
        WHEN pp.ID IS NOT NULL AND rp.ID IS NOT NULL THEN 'Popular & Recent'
        WHEN pp.ID IS NOT NULL THEN 'Popular Only'
        ELSE 'Recent Only'
    END as category
FROM popular_posts pp
FULL OUTER JOIN recent_posts rp ON pp.ID = rp.ID
ORDER BY pp.post_date DESC, rp.post_date DESC;

-- Recursive CTE for hierarchical categories
WITH RECURSIVE category_tree AS (
    SELECT term_id, name, parent, 0 as level
    FROM wp_terms t
    JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
    WHERE tt.parent = 0
    
    UNION ALL
    
    SELECT t.term_id, t.name, tt.parent, ct.level + 1
    FROM category_tree ct
    JOIN wp_term_taxonomy tt ON ct.term_id = tt.parent
    JOIN wp_terms t ON tt.term_id = t.term_id
)
SELECT * FROM category_tree
ORDER BY level, name;

JSON Data Type Optimization

-- Create table with JSON column
CREATE TABLE wp_product_attributes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT,
    attributes JSON,
    INDEX idx_attributes ((CAST(attributes->'$.price' AS DECIMAL(10,2)))),
    INDEX idx_attributes_color ((CAST(attributes->'$.color' AS CHAR(50))))
);

-- Insert JSON data
INSERT INTO wp_product_attributes (post_id, attributes) VALUES (
    123,
    JSON_OBJECT(
        'price', 99.99,
        'color', 'red',
        'size', 'XL',
        'in_stock', true,
        'tags', JSON_ARRAY('sale', 'featured')
    )
);

-- Query JSON data
SELECT * FROM wp_product_attributes
WHERE JSON_EXTRACT(attributes, '$.price') > 50
AND JSON_EXTRACT(attributes, '$.color') = 'red';

-- Update JSON
UPDATE wp_product_attributes
SET attributes = JSON_SET(attributes, '$.price', 79.99)
WHERE post_id = 123;

-- Use JSON_TABLE for relational queries
SELECT p.post_title, a.*
FROM wp_posts p
CROSS JOIN JSON_TABLE(
    (SELECT attributes FROM wp_product_attributes WHERE post_id = p.ID),
    '$' COLUMNS(
        price DECIMAL(10,2) PATH '$.price',
        color VARCHAR(50) PATH '$.color',
        in_stock BOOLEAN PATH '$.in_stock'
    )
) a
WHERE p.post_type = 'product';

Spatial Data for Geolocation

-- Add spatial columns
ALTER TABLE wp_posts 
ADD COLUMN location POINT,
ADD SPATIAL INDEX idx_location (location);

-- Store coordinates
UPDATE wp_posts 
SET location = POINT(40.7128, -74.0060) 
WHERE ID = 123;

-- Find nearby posts
SELECT 
    ID,
    post_title,
    ST_Distance_Sphere(location, POINT(40.7128, -74.0060)) as distance_meters
FROM wp_posts
WHERE ST_Distance_Sphere(location, POINT(40.7128, -74.0060)) < 5000
AND post_status = 'publish'
ORDER BY distance_meters;

-- Find posts in bounding box
SELECT ID, post_title
FROM wp_posts
WHERE MBRContains(
    ST_GeomFromText('POLYGON((40.7 -74.1, 40.7 -73.9, 40.8 -73.9, 40.8 -74.1, 40.7 -74.1))'),
    location
);

Automated Query Rewriting

-- MySQL proxy for query rewriting
-- Example with ProxySQL

INSERT INTO mysql_query_rules 
(rule_id, active, match_pattern, replace_pattern, apply)
VALUES (
    1, 1,
    'SELECT \* FROM wp_posts WHERE ID = (\d+)',
    'SELECT * FROM wp_posts_cache WHERE post_id = \1',
    1
);

-- Or use MySQL 8.0 query rewrite plugins
INSTALL PLUGIN query_rewrite SONAME 'query_rewrite.so';

INSERT INTO query_rewrite.rewrite_rules 
(pattern, replacement, pattern_database)
VALUES (
    'SELECT * FROM wp_posts ORDER BY RAND() LIMIT ?',
    'SELECT * FROM wp_posts JOIN (SELECT CEIL(RAND() * (SELECT MAX(ID) FROM wp_posts)) AS id) AS r WHERE ID >= r.id ORDER BY ID LIMIT ?',
    'wordpress'
);

CALL query_rewrite.flush_rewrite_rules();

-- PHP query interception
class DatabaseInterceptor {
    private $patterns = [
        '/ORDER BY RAND\(\)/' => function($query) {
            return str_replace(
                'ORDER BY RAND()',
                'JOIN (SELECT CEIL(RAND() * (SELECT MAX(ID) FROM wp_posts)) AS id) AS r ON ID >= r.id ORDER BY ID',
                $query
            );
        }
    ];
    
    public function query($sql) {
        foreach ($this->patterns as $pattern => $replacement) {
            if (preg_match($pattern, $sql)) {
                $sql = $replacement($sql);
                break;
            }
        }
        return $this->execute($sql);
    }
}

Bonus: 5 More Database Optimization Tips

• 51. Use pt-online-schema-change for zero-downtime schema changes
• 52. Implement table partitioning by date for logs and historical data
• 53. Use MySQL Enterprise Monitor or Percona Monitoring and Management
• 54. Consider Vitess or TiDB for horizontal scaling beyond 1TB
• 55. Implement database auditing with MariaDB Audit Plugin