Manage a composite index on the postmeta table (post_id, meta_key(191)) to speed up ACF and meta lookups. One-click, reversible. Tools screen UI.

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. Tools screen UI.
 * Author: Luis Martinez
 * Author URI: https://www.lessrain.com
 * Version: 1.3
 * Requires PHP: 7.4
 */

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

// Constants
define('LR_PMI_CACHE_GROUP', 'lr_postmeta_index');
define('LR_PMI_CACHE_TTL', 300); // 5 minutes

/**
 * Register admin menu page
 */
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-postmeta-index',
        'lr_pmi_render_tools_page'
    );
}

/**
 * Render Tools page interface
 *
 * @return void
 */
function lr_pmi_render_tools_page()
{
    if (!current_user_can('manage_options')) {
        wp_die(__('Insufficient permissions.', 'lr-postmeta-index'));
    }

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

    // Check if index exists (with caching)
    $has_index = lr_pmi_index_exists($table, true);

    // Get table size information (lazy-loaded via AJAX in future, for now conditional)
    $size_mb = null;
    if (isset($_GET['show_stats'])) {
        $size_mb = lr_pmi_get_table_size($table);
    }

    // Retrieve admin messages from query string
    $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']) : '';
    
    // Get index creation timestamp
    $created_at = get_option('lr_pmi_created_at', '');
    $when = $created_at ? esc_html(sanitize_text_field($created_at)) : '';

    ?>
    <div class="wrap">
        <h1><?php esc_html_e('Postmeta Index Manager', 'lr-postmeta-index'); ?></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; ?>

        <div class="card" style="max-width:880px;">
            <h2 class="title"><?php esc_html_e('Status', 'lr-postmeta-index'); ?></h2>
            <table class="form-table" role="presentation">
                <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> on <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): ?>
                                <span style="color:#46b450;">●</span> <?php esc_html_e('Active', 'lr-postmeta-index'); ?>
                            <?php else: ?>
                                <span style="color:#dc3232;">●</span> <?php esc_html_e('Not Created', 'lr-postmeta-index'); ?>
                            <?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; ?>
                    <?php if ($size_mb !== null): ?>
                    <tr>
                        <th scope="row"><?php esc_html_e('Table Size:', 'lr-postmeta-index'); ?></th>
                        <td><?php echo esc_html($size_mb); ?> MB</td>
                    </tr>
                    <?php elseif (!isset($_GET['show_stats'])): ?>
                    <tr>
                        <th scope="row"><?php esc_html_e('Table Size:', 'lr-postmeta-index'); ?></th>
                        <td>
                            <a href="<?php echo esc_url(add_query_arg('show_stats', '1')); ?>">
                                <?php esc_html_e('Show statistics', 'lr-postmeta-index'); ?>
                            </a>
                            <span style="opacity:.7;font-size:.9em;margin-left:.5rem;">
                                (<?php esc_html_e('may be slow on large databases', 'lr-postmeta-index'); ?>)
                            </span>
                        </td>
                    </tr>
                    <?php endif; ?>
                </tbody>
            </table>

            <form method="post" action="<?php echo esc_url(admin_url('admin-post.php')); ?>" style="margin-top:16px;">
                <?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">
                    <button type="submit" class="button button-primary">
                        <?php esc_html_e('Create Index Now', 'lr-postmeta-index'); ?>
                    </button>
                    <span style="margin-left:.5rem;opacity:.8">
                        <?php esc_html_e('Run during low-traffic periods.', 'lr-postmeta-index'); ?>
                    </span>
                <?php else: ?>
                    <input type="hidden" name="lr_pmi_do" value="drop">
                    <button type="submit" class="button">
                        <?php esc_html_e('Remove Index', 'lr-postmeta-index'); ?>
                    </button>
                <?php endif; ?>
            </form>
        </div>

        <div class="card" style="max-width:880px;">
            <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 style="background:#f5f5f5;padding:12px;border-radius:3px;overflow-x:auto;"><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. Adds minimal write overhead, but read speedup is usually significant. Fully reversible with one click.', 'lr-postmeta-index'); ?>
            </p>
            
            <?php if ($has_index): ?>
            <hr style="margin:20px 0;border:0;border-top:1px solid #ddd;">
            <h3><?php esc_html_e('Performance Impact', 'lr-postmeta-index'); ?></h3>
            <p>
                <strong><?php esc_html_e('Expected improvements:', 'lr-postmeta-index'); ?></strong>
            </p>
            <ul style="list-style:disc;margin-left:2em;">
                <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('Meta queries in WP_Query: 30-70% faster', 'lr-postmeta-index'); ?></li>
                <li><?php esc_html_e('Page load times (meta-heavy pages): 10-30% improvement', 'lr-postmeta-index'); ?></li>
            </ul>
            <p style="opacity:.8;font-size:.95em;">
                <?php esc_html_e('Actual results vary based on database size, query patterns, and server configuration.', 'lr-postmeta-index'); ?>
            </p>
            <?php endif; ?>
        </div>
    </div>
    <?php
}

/**
 * Check if the composite index exists on the postmeta table
 *
 * @param string $table Table name
 * @param bool $use_cache Whether to use cached results
 * @return bool True if index exists, false otherwise
 */
function lr_pmi_index_exists($table, $use_cache = true)
{
    $cache_key = 'index_exists_' . md5($table);
    
    if ($use_cache) {
        $cached = wp_cache_get($cache_key, LR_PMI_CACHE_GROUP);
        if ($cached !== false) {
            return (bool) $cached;
        }
    }
    
    global $wpdb;
    
    // More efficient query - only checks for existence
    $result = $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(*) FROM information_schema.statistics 
         WHERE table_schema = DATABASE()
           AND table_name = %s
           AND index_name = %s",
        $table,
        'post_id_meta_key'
    ));
    
    $exists = (bool) $result;
    
    // Cache for 5 minutes
    wp_cache_set($cache_key, $exists, LR_PMI_CACHE_GROUP, LR_PMI_CACHE_TTL);
    
    return $exists;
}

/**
 * Get approximate table size in MB
 *
 * @param string $table Table name
 * @return float|null Size in MB or null on error
 */
function lr_pmi_get_table_size($table)
{
    $cache_key = 'table_size_' . md5($table);
    
    $cached = wp_cache_get($cache_key, LR_PMI_CACHE_GROUP);
    if ($cached !== false) {
        return $cached;
    }
    
    global $wpdb;
    
    $query = $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",
        $table
    );
    
    $size = $wpdb->get_var($query);
    
    // Cache for 5 minutes
    if ($size !== null) {
        wp_cache_set($cache_key, $size, LR_PMI_CACHE_GROUP, LR_PMI_CACHE_TTL);
    }
    
    return $size;
}

/**
 * Clear all plugin caches
 *
 * @return void
 */
function lr_pmi_clear_cache()
{
    global $wpdb;
    $table = $wpdb->postmeta;
    
    wp_cache_delete('index_exists_' . md5($table), LR_PMI_CACHE_GROUP);
    wp_cache_delete('table_size_' . md5($table), LR_PMI_CACHE_GROUP);
}

/**
 * Handle index creation and removal actions
 *
 * @return void
 */
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');

    global $wpdb;
    $table = $wpdb->postmeta;
    $action = isset($_POST['lr_pmi_do']) ? sanitize_key($_POST['lr_pmi_do']) : '';

    // Prevent concurrent ALTER operations via transient lock
    $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;
    }
    
    set_transient($lock_key, 1, 60);

    // Double-check current state (bypass cache for accuracy)
    $has_index = lr_pmi_index_exists($table, false);

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

/**
 * Create the composite index on postmeta table
 *
 * @param string $table Table name
 * @param string $lock_key Transient lock key
 * @return void
 */
function lr_pmi_create_index($table, $lock_key)
{
    global $wpdb;
    
    // Attempt with 191 character prefix (utf8mb4 safe)
    $query = "ALTER TABLE `" . esc_sql($table) . "` ADD INDEX post_id_meta_key (post_id, meta_key(191))";
    $result = $wpdb->query($query);

    // Fallback to 190 if key length error occurs
    if ($result === false && lr_pmi_is_key_too_long_error($wpdb->last_error)) {
        $query = "ALTER TABLE `" . esc_sql($table) . "` 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(); // Invalidate cache after structure change
        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 postmeta table
 *
 * @param string $table Table name
 * @param string $lock_key Transient lock key
 * @return void
 */
function lr_pmi_drop_index($table, $lock_key)
{
    global $wpdb;
    
    $query = "ALTER TABLE `" . esc_sql($table) . "` DROP INDEX post_id_meta_key";
    $result = $wpdb->query($query);

    delete_transient($lock_key);

    if ($result !== false) {
        delete_option('lr_pmi_created_at');
        lr_pmi_clear_cache(); // Invalidate cache after structure change
        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 MySQL error indicates 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)
{
    // MySQL error code 1071: Specified key was too long
    // Works across MySQL versions and languages
    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';
    
    $url = add_query_arg(
        [
            'page' => 'lr-postmeta-index',
            $query_arg => rawurlencode($message),
        ],
        admin_url('tools.php')
    );
    
    wp_safe_redirect($url);
    exit;
}