Optimize WordPress MySQL tables through Cron, behind the scenes

Schedule WordPress database optimization with WP-Cron.

Saotn MySQL Database Optimizer Must Use Plugin

Keeping your WordPress MySQL database optimized is a must for a fast performing website. Every post, draft, revision and comment adds data to your database, and removing revisions, drafts and comments creates empty space between data. This makes your database huger and fragmented.

speaking of post revisions, did you know you can easily delete old WordPress post revisions?

Defragmenting the data puts all data back in order, removing empty spaces and results in a smaller database. The defragmented database performs faster because MySQL will be able locate information much faster.

You can use this as a must-use plugin by placing the file in the wp-content/mu-plugins directory, or as a regular plugin in wp-content/plugins (create a folder saotn-optimizer yourself), which you then must activate through the WordPress Dashboard.

You are free to use, change and customize the plugin code. I've based it off Pressjitsu's transient cleaner plugin. All the plugin does is executing an OPTIMIZE TABLE statement for every table in your WordPress MySQL database on a daily or weekly basis (yours to choose).

<?php
/**
 * Plugin Name: Saotn Database Table Optimizer
 * Plugin URI: https://www.saotn.org
 * Donate Link: https://www.paypal.me/jreilink
 * Description: Optimizes WordPress MySQL database behind the scenes by executing an OPTIMIZE TABLE statement on all MySQL tables, 'daily' or 'hourly'.
 * Version: 1.0.2
 * Author: Jan Reilink
 * Author URI: https://www.saotn.org
 * License: GPLv2
 */

class Saotn_Db_Optimize_Tables_Cron {
  public static function load() {
    add_action( 'init', array( __CLASS__, 'schedule_events' ) );
    add_filter( 'plugin_row_meta', 'prefix_plugin_row_meta', 10, 2 );
  }

  /**
   * Schedule cron events, runs during init.
   */
  public static function schedule_events() {
    if ( ! wp_next_scheduled( 'saotn_db_optimize_tables_cron' ) )
      // wp_schedule_event( time(), 'daily', 'saotn_db_optimize_tables_cron' );
      wp_schedule_event( time(), 'hourly', 'saotn_db_optimize_tables_cron' );
      add_action( 'saotn_db_optimize_tables_cron', array( __CLASS__, 'optimize_tables' ) );
  }

  public static function optimize_tables() {
    global $wpdb;
    $bDebug = TRUE;
    $tables = $wpdb->get_col( "SHOW TABLES" );
    foreach ( $tables as $table ) {
      if ( $wpdb->query( "OPTIMIZE TABLE $table" ) !== FALSE ) {
        if ( $bDebug ) {
          error_log( "Saotn_Db_Optimizer ran successfully on $table" );
        }
      }
    }
  }
}

Saotn_Db_Optimize_Tables_Cron::load();

// sdto = saotn database table optimizer
add_filter( 'plugin_row_meta', 'sdto_plugin_row_meta', 10, 2 );
function sdto_plugin_row_meta($links, $file) {
  if ( !preg_match('/wp-database-tables-optimizer.php$/', $file ) ) {
    return $links;
  }

  $links[] = sprintf(
    '<a target="_blank" href="https://paypal.me/jreilink" title="Donate to Jan Reilink / Sysadmins of the North">%s</a>',
    __( 'Donate' )
  );
  return $links;
}

Use a plugin like Advanced Cron Manager to verify this database optimization WordPress Cron.

Optimize WordPress database alternatives:
Two alternatives for my very basic must-use plugin are WP Sweep and WP Optimize.