MySQL Stored Procedures for WordPress Database Maintenance Tasks

A MySQL stored procedure is a named, reusable block of SQL logic stored in the database and executed with a single CALL procedure_name(); — it accepts IN (read-only), OUT (write-back), and INOUT (read-write) parameters, supports local variables (DECLARE @var TYPE DEFAULT value;), IF / ELSEIF / ELSE branching, WHILE and REPEAT loops, and cursors for row-by-row iteration. For WordPress database administration, stored procedures are useful for complex multi-statement maintenance operations that would otherwise require long, error-prone SQL scripts or PHP scripts to orchestrate: cleaning orphaned post meta, bulk-archiving old posts, rebuilding term counts, or deleting expired transients in batches to avoid locking. Stored procedures run entirely inside the MySQL server, reducing round-trip latency compared to executing the same logic as multiple separate queries from PHP or the command line — a procedure that deletes 500,000 rows in 1,000-row batches executes all 500 DELETE statements without any network round-trip between iterations. The DELIMITER command changes the statement terminator so that the ; inside the procedure body is not interpreted as the end of the CREATE PROCEDURE statement — this is only necessary in the mysql CLI; the MySQL Workbench and HeidiSQL GUI tools handle it automatically. WordPress stores transients in wp_options as rows with option_name LIKE '_transient_%' and separate timeout rows with option_name LIKE '_transient_timeout_%' — expired transients are not automatically removed until a request triggers the transient’s expiration check, so long-lived sites accumulate hundreds of thousands of orphaned rows that slow down every query against wp_options. Stored procedures called from wp_schedule_event’s scheduled task via a PHP $wpdb->query( “CALL cleanup_expired_transients()” ) provide a safe batched cleanup. The MySQL Window Functions and CTEs post covered advanced read-only queries; stored procedures extend that with writable multi-statement logic encapsulated server-side.

Problem: A WordPress site that has been live for four years has 1.2 million rows in wp_options — 800,000 of them are expired transients. A single DELETE FROM wp_options WHERE option_name LIKE ‘_transient%’ runs for 40 seconds and locks the table, causing a site outage. WP-Cron runs delete_expired_transients() but only clears transients that were accessed since the last cron run.

Solution: Create a stored procedure that deletes expired transients in configurable batches with a sleep between iterations to avoid prolonged lock time, then call it from a WP-CLI command or a weekly WordPress cron event.

-- ── Create the batched cleanup stored procedure ───────────────────────────────
DELIMITER $$

CREATE PROCEDURE IF NOT EXISTS cleanup_expired_transients(
    IN batch_size  INT,     -- rows to delete per iteration (e.g. 500)
    IN sleep_secs  DECIMAL(5,2),  -- seconds to sleep between batches (e.g. 0.05)
    OUT deleted_total INT   -- total rows deleted (OUT parameter)
)
BEGIN
    DECLARE rows_deleted INT DEFAULT 0;
    SET deleted_total = 0;

    -- Loop: delete one batch of timeout rows that have already expired,
    -- then delete the matching transient value rows.
    -- Repeat until no more expired timeouts remain.
    delete_loop: LOOP
        -- Step 1: Delete expired timeout meta rows
        DELETE FROM wp_options
        WHERE  option_name LIKE '_transient_timeout_%'
          AND  CAST(option_value AS UNSIGNED) < UNIX_TIMESTAMP()
        LIMIT  batch_size;

        SET rows_deleted = ROW_COUNT();
        SET deleted_total = deleted_total + rows_deleted;

        -- Step 2: Delete orphaned transient value rows (no matching timeout row)
        DELETE v FROM wp_options v
        LEFT JOIN wp_options t
            ON t.option_name = CONCAT('_transient_timeout_', SUBSTRING(v.option_name, 12))
        WHERE v.option_name LIKE '_transient_%'
          AND v.option_name NOT LIKE '_transient_timeout_%'
          AND t.option_id IS NULL
        LIMIT batch_size;

        SET deleted_total = deleted_total + ROW_COUNT();

        -- Stop when last iteration deleted nothing
        IF rows_deleted = 0 THEN
            LEAVE delete_loop;
        END IF;

        -- Yield lock between batches to allow concurrent writes
        DO SLEEP(sleep_secs);
    END LOOP;
END$$

DELIMITER ;

-- ── Create a procedure to rebuild all term counts ─────────────────────────────
DELIMITER $$

CREATE PROCEDURE IF NOT EXISTS rebuild_term_counts()
BEGIN
    UPDATE wp_term_taxonomy tt
    JOIN (
        SELECT   term_taxonomy_id, COUNT(*) AS cnt
        FROM     wp_term_relationships tr
        JOIN     wp_posts p ON p.ID = tr.object_id
        WHERE    p.post_status = 'publish'
          AND    p.post_type   NOT IN ('revision','nav_menu_item','attachment')
        GROUP BY term_taxonomy_id
    ) counts ON counts.term_taxonomy_id = tt.term_taxonomy_id
    SET tt.count = counts.cnt;

    -- Zero out terms that have no published posts
    UPDATE wp_term_taxonomy
    SET    count = 0
    WHERE  term_taxonomy_id NOT IN (
        SELECT DISTINCT term_taxonomy_id FROM wp_term_relationships
    );
END$$

DELIMITER ;

// ── Call stored procedures from PHP (WordPress) ───────────────────────────────

// Call from a WP-Cron event (scheduled weekly)
add_action( 'myplugin_weekly_cleanup', 'myplugin_run_db_maintenance' );

function myplugin_run_db_maintenance(): void {
    global $wpdb;

    // Call stored procedure with batch_size=500, sleep=0.05s
    // @deleted_total is an OUT parameter — retrieve it with a SELECT
    $wpdb->query( "CALL cleanup_expired_transients(500, 0.05, @deleted_total)" );

    $deleted = (int) $wpdb->get_var( "SELECT @deleted_total" );
    if ( $deleted > 0 ) {
        error_log( sprintf( 'DB maintenance: deleted %d expired transient rows', $deleted ) );
    }
}

// Register the weekly cron event on plugin activation
register_activation_hook( __FILE__, function(): void {
    if ( ! wp_next_scheduled( 'myplugin_weekly_cleanup' ) ) {
        wp_schedule_event( time(), 'weekly', 'myplugin_weekly_cleanup' );
    }
} );
register_deactivation_hook( __FILE__, function(): void {
    wp_clear_scheduled_hook( 'myplugin_weekly_cleanup' );
} );

# Call the procedure directly from the MySQL CLI
/Applications/XAMPP/xamppfiles/bin/mysql -u root helloadmin     -e "CALL cleanup_expired_transients(500, 0.05, @total); SELECT @total AS deleted;"

# Call from WP-CLI (wraps $wpdb->query)
wp db query "CALL cleanup_expired_transients(500, 0.05, @total); SELECT @total;"

# List all stored procedures in the database
wp db query "SHOW PROCEDURE STATUS WHERE Db = DATABASE()\G"

NOTE: Stored procedures are database-level objects — they are not included in WordPress’s standard dbDelta() schema management or in a typical WP-CLI wp db export SQL dump unless you pass --routines to mysqldump: mysqldump --routines helloadmin > backup.sql. This means a staging-to-production migration that uses only table exports will not carry stored procedures over — document them in a plugin activation hook that creates them with $wpdb->query( "CREATE PROCEDURE IF NOT EXISTS ..." ) so they are recreated on any new install. Also, DELIMITER $$ is a MySQL CLI client directive, not valid SQL — when creating procedures via $wpdb->query() in PHP, omit the DELIMITER lines entirely and pass only the CREATE PROCEDURE ... END block as the query string.