Skip to content

Instantly share code, notes, and snippets.

@arenagroove

arenagroove/lr-postmeta-index.php

Last active March 16, 2026 15:00
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save arenagroove/ec9c4d6db38dd1b502dd4864b4cc00a5 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save arenagroove/ec9c4d6db38dd1b502dd4864b4cc00a5 to your computer and use it in GitHub Desktop.
Download ZIP
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.
Raw
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.5
* Requires PHP: 7.4
* Requires at least: 6.2
*/
if (!defined('ABSPATH')) {
exit;
}
/**
* 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;
$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 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(['page' => 'lr-postmeta-index', 'show_stats' => '1'], admin_url('tools.php'))); ?>">
<?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. 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 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 (on sites with 10,000+ posts):', '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('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 style="opacity:.8;font-size:.95em;">
<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>
</div>
<?php
}
/**
* Check if a composite index on (post_id, meta_key) already exists on the postmeta table,
* regardless of index name. Uses SHOW INDEX to avoid information_schema permission issues
* on managed/restricted hosts. Detects by column composition and sequence, not by name,
* to prevent creating duplicate indexes if another plugin or DBA created the same structure
* under a different name.
*
* @param bool $use_cache Whether to use cached results
* @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';
}
}
global $wpdb;
$rows = $wpdb->get_results("SHOW INDEX FROM `{$wpdb->postmeta}`");
$found = false;
if ($rows) {
$indexes = [];
foreach ($rows as $row) {
$indexes[$row->Key_name][(int) $row->Seq_in_index] = $row->Column_name;
}
foreach ($indexes as $cols) {
ksort($cols);
$cols = array_values($cols);
if (
isset($cols[0], $cols[1]) &&
$cols[0] === 'post_id' &&
$cols[1] === 'meta_key'
) {
$found = true;
break;
}
}
}
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');
}
/**
* 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');
$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 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 postmeta table
*
* @param string $lock_key Transient lock key
* @return void
* @throws Exception If index removal fails
*/
function lr_pmi_drop_index($lock_key)
{
global $wpdb;
$query = "ALTER TABLE `{$wpdb->postmeta}` 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();
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)
{
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-postmeta-index',
$query_arg => $message,
],
admin_url('tools.php')
);
wp_safe_redirect($url);
exit;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment