WordPress MU plugin for safely managing composite postmeta indexes to improve ACF and meta query performance on large WordPress sites.

lr-postmeta-index.php

<?php
/**
 * Plugin Name:  LR Postmeta Index Manager
 * Description:  Manage a composite index on the postmeta table (post_id, meta_key(191)) to speed up ACF and meta lookups. One-click, reversible.
 * Version:      1.6.0
 * Requires PHP: 7.4
 * Requires at least: 6.2
 * Author:       Luis Martinez
 * Author URI:   https://www.lessrain.com
 *
 * Changelog
 * ─────────────────────────────────────────────────────────────────────────────
 * 1.6.0  2026-05-22  Updated to unified LR admin UI standard. Bug fix: index
 *                    removal now resolves the live index name via SHOW INDEX
 *                    (lr_pmi_get_index_name) instead of assuming the hardcoded
 *                    post_id_meta_key name, aligning drop behaviour with the
 *                    composition-based detection in lr_pmi_index_exists.
 *                    Extracted lr_pmi_get_index_name() to eliminate duplicated
 *                    SHOW INDEX traversal. Remove Index moved to red destructive
 *                    link with confirm gate.
 * 1.5.0  —           Previous version (no changelog on record).
 */

if ( ! defined( 'ABSPATH' ) ) exit;

/* =============================================================
   Constants
============================================================= */

define( 'LR_PMI_VERSION', '1.6.0' );
define( 'LR_PMI_SLUG',    'lr-postmeta-index' );

/* =============================================================
   Admin menu
============================================================= */

add_action( 'admin_menu', 'lr_pmi_register_admin_menu' );

function lr_pmi_register_admin_menu() {
    add_management_page(
        'LR Postmeta Index',
        'LR Postmeta Index',
        'manage_options',
        LR_PMI_SLUG,
        'lr_pmi_render_tools_page'
    );
}

/* =============================================================
   Settings page styles
============================================================= */

add_action( 'admin_head', function () {
    $screen = get_current_screen();
    if ( ! $screen || $screen->id !== 'tools_page_' . LR_PMI_SLUG ) return;
    ?>
    <style>
        #pmi-wrap { max-width: 880px; }
        #pmi-wrap h1 .lr-version { font-size: 12px; font-weight: 400; color: #646970; margin-left: 6px; }
        #pmi-wrap h2.title { margin-top: 1.5em; }
        #pmi-wrap hr { margin: 2em 0; border: none; border-top: 1px solid #c3c4c7; }
        #pmi-wrap .lr-status-off { color: #d63638; }
        #pmi-wrap .lr-remove { color: #d63638; }
        #pmi-wrap pre { padding: 12px; overflow-x: auto; border: 1px solid #c3c4c7; }
        #pmi-wrap .lr-perf-list { list-style: disc; margin-left: 2em; }
    </style>
    <?php
} );

/* =============================================================
   Tools page
============================================================= */

function lr_pmi_render_tools_page() {
    if ( ! current_user_can( 'manage_options' ) ) {
        wp_die( __( 'Insufficient permissions.', 'lr-postmeta-index' ) );
    }

    global $wpdb;
    $table = $wpdb->postmeta;

    $has_index = lr_pmi_index_exists();

    $size_mb = null;
    if ( isset( $_GET['show_stats'] ) ) {
        $size_mb = lr_pmi_get_table_size();
    }

    $msg = isset( $_GET['lr_pmi_msg'] ) ? sanitize_text_field( $_GET['lr_pmi_msg'] ) : '';
    $err = isset( $_GET['lr_pmi_err'] ) ? sanitize_text_field( $_GET['lr_pmi_err'] ) : '';

    $created_at = get_option( 'lr_pmi_created_at', '' );
    $when       = $created_at ? esc_html( sanitize_text_field( $created_at ) ) : '';
    ?>
    <div id="pmi-wrap" class="wrap">

        <h1><?php esc_html_e( 'Postmeta Index Manager', 'lr-postmeta-index' ); ?> <span class="lr-version">v<?php echo esc_html( LR_PMI_VERSION ); ?></span></h1>

        <?php if ( $msg ): ?>
            <div class="notice notice-success is-dismissible"><p><?php echo esc_html( $msg ); ?></p></div>
        <?php endif; ?>

        <?php if ( $err ): ?>
            <div class="notice notice-error"><p><?php echo esc_html( $err ); ?></p></div>
        <?php endif; ?>

        <?php /* ─── Status ───────────────────────────────── */ ?>

        <h2 class="title"><?php esc_html_e( 'Status', 'lr-postmeta-index' ); ?></h2>

        <table class="widefat striped">
            <tbody>
                <tr>
                    <th scope="row"><?php esc_html_e( 'Table', 'lr-postmeta-index' ); ?></th>
                    <td><code><?php echo esc_html( $table ); ?></code></td>
                </tr>
                <tr>
                    <th scope="row"><?php esc_html_e( 'Index', 'lr-postmeta-index' ); ?></th>
                    <td><code>post_id_meta_key</code> <?php esc_html_e( 'on', 'lr-postmeta-index' ); ?> <code>(post_id, meta_key(191))</code></td>
                </tr>
                <tr>
                    <th scope="row"><?php esc_html_e( 'Status', 'lr-postmeta-index' ); ?></th>
                    <td>
                        <?php if ( $has_index ): ?>
                            <?php esc_html_e( 'Active', 'lr-postmeta-index' ); ?>
                        <?php else: ?>
                            <span class="lr-status-off"><?php esc_html_e( 'Not created', 'lr-postmeta-index' ); ?></span>
                        <?php endif; ?>
                    </td>
                </tr>
                <?php if ( $when ): ?>
                <tr>
                    <th scope="row"><?php esc_html_e( 'Created', 'lr-postmeta-index' ); ?></th>
                    <td><?php echo $when; ?></td>
                </tr>
                <?php endif; ?>
                <tr>
                    <th scope="row"><?php esc_html_e( 'Table Size', 'lr-postmeta-index' ); ?></th>
                    <td>
                        <?php if ( $size_mb !== null ): ?>
                            <?php echo esc_html( $size_mb ); ?> MB
                        <?php elseif ( ! isset( $_GET['show_stats'] ) ): ?>
                            <a href="<?php echo esc_url( add_query_arg( [ 'page' => LR_PMI_SLUG, 'show_stats' => '1' ], admin_url( 'tools.php' ) ) ); ?>">
                                <?php esc_html_e( 'Show statistics', 'lr-postmeta-index' ); ?>
                            </a>
                            <span class="description"><?php esc_html_e( '(may be slow on large databases)', 'lr-postmeta-index' ); ?></span>
                        <?php endif; ?>
                    </td>
                </tr>
            </tbody>
        </table>

        <form method="post" action="<?php echo esc_url( admin_url( 'admin-post.php' ) ); ?>">
            <?php wp_nonce_field( 'lr_pmi_action', 'lr_pmi_nonce' ); ?>
            <input type="hidden" name="action" value="lr_pmi_handle">

            <?php if ( ! $has_index ): ?>
                <input type="hidden" name="lr_pmi_do" value="create">
                <p>
                    <button type="submit" class="button button-primary">
                        <?php esc_html_e( 'Create Index Now', 'lr-postmeta-index' ); ?>
                    </button>
                    <span class="description"><?php esc_html_e( 'Run during low-traffic periods.', 'lr-postmeta-index' ); ?></span>
                </p>
            <?php else: ?>
                <input type="hidden" name="lr_pmi_do" value="drop">
                <p>
                    <a href="#" id="pmi_remove" class="lr-remove"><?php esc_html_e( 'Remove Index', 'lr-postmeta-index' ); ?></a>
                </p>
            <?php endif; ?>
        </form>

        <hr>

        <?php /* ─── What This Does ──────────────────────── */ ?>

        <h2 class="title"><?php esc_html_e( 'What This Does', 'lr-postmeta-index' ); ?></h2>
        <p><?php esc_html_e( 'Adds a composite index so MySQL can find meta rows quickly, optimizing queries like:', 'lr-postmeta-index' ); ?></p>
        <pre><code>SELECT meta_value FROM <?php echo esc_html( $table ); ?>

WHERE post_id = ? AND meta_key = ?;</code></pre>
        <p><?php esc_html_e( 'Safe for ACF, Polylang, and most meta-heavy plugins. Read speedup is usually significant on sites with 10,000+ posts. Fully reversible with one click.', 'lr-postmeta-index' ); ?></p>

        <?php if ( $has_index ): ?>

        <hr>

        <h2 class="title"><?php esc_html_e( 'Performance Impact', 'lr-postmeta-index' ); ?></h2>
        <p><strong><?php esc_html_e( 'Expected improvements (on sites with 10,000+ posts):', 'lr-postmeta-index' ); ?></strong></p>
        <ul class="lr-perf-list">
            <li><?php esc_html_e( 'ACF field lookups: 50–90% faster', 'lr-postmeta-index' ); ?></li>
            <li><?php esc_html_e( 'get_post_meta() calls: 40–80% faster', 'lr-postmeta-index' ); ?></li>
            <li><?php esc_html_e( 'WP_Query meta queries: 30–70% faster (varies by query complexity)', 'lr-postmeta-index' ); ?></li>
            <li><?php esc_html_e( 'Page load times (meta-heavy pages): 10–30% improvement', 'lr-postmeta-index' ); ?></li>
        </ul>
        <p class="description">
            <strong><?php esc_html_e( 'Note:', 'lr-postmeta-index' ); ?></strong>
            <?php esc_html_e( 'Adds approximately 5–10% overhead to INSERT/UPDATE/DELETE operations on postmeta. Actual results vary based on database size, query patterns, and server configuration.', 'lr-postmeta-index' ); ?>
        </p>

        <?php endif; ?>

    </div>
    <?php if ( $has_index ): ?>
    <script>
    (function () {
        var ui = {
            remove: document.getElementById('pmi_remove'),
            init: function () {
                if (!this.remove) return;
                this.remove.addEventListener('click', function (e) {
                    e.preventDefault();
                    if (!confirm('<?php echo esc_js( __( 'Remove the postmeta index? This will affect query performance until recreated.', 'lr-postmeta-index' ) ); ?>')) return;
                    ui.remove.closest('form').submit();
                });
            }
        };
        ui.init();
    }());
    </script>
    <?php endif; ?>
    <?php
}

/* =============================================================
   Index detection helpers
============================================================= */

/**
 * Return the name of the composite (post_id, meta_key) index on the postmeta
 * table, regardless of what it was named. Returns null if no such index exists.
 *
 * Uses SHOW INDEX to avoid information_schema permission issues on managed/
 * restricted hosts. Detects by column composition and sequence, not by name,
 * so externally-created indexes with different names are correctly identified.
 *
 * @return string|null Index name, or null if not found.
 */
function lr_pmi_get_index_name() {
    global $wpdb;

    $rows = $wpdb->get_results( "SHOW INDEX FROM `{$wpdb->postmeta}`" );
    if ( ! $rows ) return null;

    $indexes = [];
    foreach ( $rows as $row ) {
        $indexes[ $row->Key_name ][ (int) $row->Seq_in_index ] = $row->Column_name;
    }

    foreach ( $indexes as $name => $cols ) {
        ksort( $cols );
        $cols = array_values( $cols );

        if (
            isset( $cols[0], $cols[1] ) &&
            $cols[0] === 'post_id' &&
            $cols[1] === 'meta_key'
        ) {
            return $name;
        }
    }

    return null;
}

/**
 * Check if a composite index on (post_id, meta_key) already exists on the
 * postmeta table. Delegates detection to lr_pmi_get_index_name() to avoid
 * duplicating the SHOW INDEX traversal. Result is cached in a short-lived
 * transient.
 *
 * @param bool $use_cache Whether to use the cached result.
 * @return bool True if a matching composite index exists, false otherwise.
 */
function lr_pmi_index_exists( $use_cache = true ) {
    $cache_key = 'lr_pmi_index_exists';

    if ( $use_cache ) {
        $cached = get_transient( $cache_key );
        if ( $cached !== false ) {
            return $cached === 'yes';
        }
    }

    $found = lr_pmi_get_index_name() !== null;
    set_transient( $cache_key, $found ? 'yes' : 'no', 5 * MINUTE_IN_SECONDS );

    return $found;
}

/**
 * Get approximate table size in MB.
 *
 * @return float|null Size in MB, or null on error.
 */
function lr_pmi_get_table_size() {
    $cache_key = 'lr_pmi_table_size';

    $cached = get_transient( $cache_key );
    if ( $cached !== false ) {
        return $cached;
    }

    global $wpdb;

    $size = $wpdb->get_var( $wpdb->prepare(
        "SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
         FROM information_schema.tables
         WHERE table_schema = DATABASE()
           AND table_name = %s",
        $wpdb->postmeta
    ) );

    if ( $size !== null ) {
        set_transient( $cache_key, $size, 5 * MINUTE_IN_SECONDS );
    }

    return $size;
}

/**
 * Clear all plugin caches.
 *
 * @return void
 */
function lr_pmi_clear_cache() {
    delete_transient( 'lr_pmi_index_exists' );
    delete_transient( 'lr_pmi_table_size' );
}

/* =============================================================
   Action handler — create / drop
============================================================= */

add_action( 'admin_post_lr_pmi_handle', 'lr_pmi_handle_action' );

function lr_pmi_handle_action() {
    if ( ! current_user_can( 'manage_options' ) ) {
        wp_die( __( 'Insufficient permissions.', 'lr-postmeta-index' ) );
    }

    check_admin_referer( 'lr_pmi_action', 'lr_pmi_nonce' );

    $action = isset( $_POST['lr_pmi_do'] ) ? sanitize_key( $_POST['lr_pmi_do'] ) : '';

    $lock_key = 'lr_pmi_lock';
    if ( get_transient( $lock_key ) ) {
        lr_pmi_redirect(
            __( 'Another operation is in progress. Please try again in a moment.', 'lr-postmeta-index' ),
            true
        );
        return;
    }

    // v1.5: extended from 60s — ALTER TABLE on large postmeta tables can take several minutes
    set_transient( $lock_key, 1, 5 * MINUTE_IN_SECONDS );

    $has_index = lr_pmi_index_exists( false );

    try {
        if ( $action === 'create' && ! $has_index ) {
            lr_pmi_create_index( $lock_key );
        } elseif ( $action === 'drop' && $has_index ) {
            lr_pmi_drop_index( $lock_key );
        } else {
            delete_transient( $lock_key );
            lr_pmi_redirect( __( 'No changes were made.', 'lr-postmeta-index' ) );
        }
    } catch ( Exception $e ) {
        delete_transient( $lock_key );
        error_log( 'LR PMI Error: ' . $e->getMessage() );
        lr_pmi_redirect( $e->getMessage(), true );
    }
}

/**
 * Create the composite index on the postmeta table.
 *
 * @param string $lock_key Transient lock key.
 * @return void
 * @throws Exception If index creation fails.
 */
function lr_pmi_create_index( $lock_key ) {
    global $wpdb;

    $query  = "ALTER TABLE `{$wpdb->postmeta}` ADD INDEX post_id_meta_key (post_id, meta_key(191))";
    $result = $wpdb->query( $query );

    if ( $result === false && lr_pmi_is_key_too_long_error( $wpdb->last_error ) ) {
        $query  = "ALTER TABLE `{$wpdb->postmeta}` ADD INDEX post_id_meta_key (post_id, meta_key(190))";
        $result = $wpdb->query( $query );
    }

    delete_transient( $lock_key );

    if ( $result !== false ) {
        update_option( 'lr_pmi_created_at', current_time( 'mysql' ), false );
        lr_pmi_clear_cache();
        lr_pmi_redirect( __( 'Postmeta index created successfully.', 'lr-postmeta-index' ) );
    } else {
        $error_msg = $wpdb->last_error
            ? sprintf( __( 'Database error: %s', 'lr-postmeta-index' ), $wpdb->last_error )
            : __( 'Unknown error occurred while creating index.', 'lr-postmeta-index' );

        throw new Exception( $error_msg );
    }
}

/**
 * Drop the composite index from the postmeta table.
 *
 * Resolves the live index name via lr_pmi_get_index_name() so that indexes
 * created externally under a different name are correctly dropped, consistent
 * with how lr_pmi_index_exists() detects them by column composition.
 *
 * @param string $lock_key Transient lock key.
 * @return void
 * @throws Exception If index removal fails.
 */
function lr_pmi_drop_index( $lock_key ) {
    global $wpdb;

    $index_name = lr_pmi_get_index_name();

    if ( ! $index_name ) {
        delete_transient( $lock_key );
        lr_pmi_redirect( __( 'No matching index found to remove.', 'lr-postmeta-index' ) );
        return;
    }

    $query  = "ALTER TABLE `{$wpdb->postmeta}` DROP INDEX `" . esc_sql( $index_name ) . "`";
    $result = $wpdb->query( $query );

    delete_transient( $lock_key );

    if ( $result !== false ) {
        delete_option( 'lr_pmi_created_at' );
        lr_pmi_clear_cache();
        lr_pmi_redirect( __( 'Postmeta index removed successfully.', 'lr-postmeta-index' ) );
    } else {
        $error_msg = $wpdb->last_error
            ? sprintf( __( 'Database error: %s', 'lr-postmeta-index' ), $wpdb->last_error )
            : __( 'Unknown error occurred while dropping index.', 'lr-postmeta-index' );

        throw new Exception( $error_msg );
    }
}

/**
 * Check if a MySQL error indicates a key length issue.
 *
 * @param string $error MySQL error message.
 * @return bool True if error is related to key length.
 */
function lr_pmi_is_key_too_long_error( $error ) {
    return stripos( $error, '1071' ) !== false || stripos( $error, 'key was too long' ) !== false;
}

/**
 * Redirect back to the Tools page with a message.
 *
 * @param string $message  Message to display.
 * @param bool   $is_error Whether this is an error message.
 * @return void
 */
function lr_pmi_redirect( $message, $is_error = false ) {
    $query_arg = $is_error ? 'lr_pmi_err' : 'lr_pmi_msg';

    // v1.5: removed rawurlencode() — add_query_arg() handles encoding internally;
    // double-encoding produced garbled notices (%2520 instead of %20)
    $url = add_query_arg(
        [
            'page'     => LR_PMI_SLUG,
            $query_arg => $message,
        ],
        admin_url( 'tools.php' )
    );

    wp_safe_redirect( $url );
    exit;
}