Database optimization in Wordpress - Mini Project: Build & Apply

Database Optimization in WordPress

📖 Scenario: You manage a WordPress website that has grown large and slow. You want to optimize the database queries to improve site speed and user experience.

🎯 Goal: Build a simple WordPress plugin that optimizes database queries by cleaning up post revisions and limiting query results.

📋 What You'll Learn

Create a function to delete old post revisions

Add a configuration variable to set the maximum number of posts per page

Write a function to limit the number of posts returned by a query

Hook the functions properly into WordPress actions

💡 Why This Matters

🌍 Real World

WordPress sites often slow down due to large numbers of post revisions and heavy queries. Cleaning revisions and limiting query results improves speed and user experience.

💼 Career

WordPress developers and site admins need to optimize database performance to maintain fast, scalable websites.

1

Create a function to delete old post revisions

Create a function called delete_old_revisions that deletes post revisions older than 30 days using wpdb and SQL DELETE query.

Wordpress

<?php
// Plugin header
/*
Plugin Name: DB Optimizer
Description: Optimize WordPress database queries
Version: 1.0
*/

// Your code here

Need a hint?

Use the global $wpdb object to run a SQL DELETE query targeting revisions older than 30 days.

2

Add a configuration variable for max posts per page

Add a variable called $max_posts and set it to 5 to limit the number of posts shown per page.

Wordpress

query("DELETE FROM {$wpdb->posts} WHERE post_type = 'revision' AND post_date < NOW() - INTERVAL 30 DAY");
}

// Your code here

Need a hint?

Define a variable $max_posts and assign it the value 5.

3

Write a function to limit posts returned by query

Create a function called limit_posts_query that accepts a $query parameter and sets $query->set('posts_per_page', $max_posts) to limit posts returned. Use the global $max_posts inside the function.

Wordpress

query("DELETE FROM {$wpdb->posts} WHERE post_type = 'revision' AND post_date < NOW() - INTERVAL 30 DAY");
}

$max_posts = 5;

// Your code here

Need a hint?

Use global $max_posts inside the function and check $query->is_main_query() and !is_admin() before setting posts_per_page.

4

Hook functions into WordPress actions

Use add_action to hook delete_old_revisions to 'wp_scheduled_delete' and add_action to hook limit_posts_query to 'pre_get_posts'.

Wordpress

query("DELETE FROM {$wpdb->posts} WHERE post_type = 'revision' AND post_date is_main_query() && !is_admin()) {
        $query->set('posts_per_page', $max_posts);
    }
}

// Your code here

Need a hint?

Use add_action to connect your functions to the correct WordPress hooks.