WP Postmeta Benchmark Test Tool for Trac Ticket #45354

test-45354.php

<?php
/**
 * WP Postmeta Benchmark Test Tool for Trac Ticket #45354
 * https://core.trac.wordpress.org/ticket/45354
 */

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

// ─── Configuration ────────────────────────────────────────────────────────────
define( 'BENCH_POST_COUNT', 500 );
define( 'BENCH_META_PER_POST', 15 );
define( 'BENCH_BATCH_SIZE', 200 );
define( 'BENCH_ITERATIONS', 100 );
define( 'BENCH_MARKER', 'bench_45354' );
// ──────────────────────────────────────────────────────────────────────────────

add_action(
	'admin_menu',
	function () {
		add_management_page(
			'Postmeta Benchmark #45354',
			'Postmeta Benchmark',
			'manage_options',
			'bench-45354',
			'bench_45354_page'
		);
	}
);

function bench_45354_page() {
	if ( ! current_user_can( 'manage_options' ) ) {
		return;
	}

	$action = '';
	if ( isset( $_POST['bench_action'] ) && check_admin_referer( 'bench_45354' ) ) {
		$action = sanitize_key( $_POST['bench_action'] );
	}

	$output = '';
	if ( 'seed' === $action ) {
		$output = bench_45354_do_seed();
	}
	if ( 'run' === $action ) {
		$output = bench_45354_do_run();
	}
	if ( 'schema' === $action ) {
		$output = bench_45354_do_schema();
	}
	if ( 'cleanup' === $action ) {
		$output = bench_45354_do_cleanup();
	}

	?>
	<div class="wrap">
		<h1>Postmeta Benchmark
			<a href="https://core.trac.wordpress.org/ticket/45354" target="_blank"
				style="font-size:13px;font-weight:normal;vertical-align:middle;margin-left:8px">
				Trac #45354 &#8599;
			</a>
		</h1>

		<form method="post" style="margin:1.2em 0 1.8em">
			<?php wp_nonce_field( 'bench_45354' ); ?>
			<button name="bench_action" value="seed"    class="button button-secondary">Seed data</button>
			&nbsp;
			<button name="bench_action" value="schema"  class="button button-secondary">Apply schema (dbDelta)</button>
			&nbsp;
			<button name="bench_action" value="run"     class="button button-primary">Run benchmark</button>
			&nbsp;
			<button name="bench_action" value="cleanup" class="button button-secondary">Cleanup</button>
		</form>

		<?php
		if ( $output ) {
			echo $output;}
		?>
	</div>
	<?php
}

// ─── Seeder ───────────────────────────────────────────────────────────────────

function bench_45354_do_seed() {
	global $wpdb;

	$existing = (int) $wpdb->get_var(
		$wpdb->prepare( "SELECT COUNT(*) FROM {$wpdb->postmeta} WHERE meta_key = %s", BENCH_MARKER )
	);
	if ( $existing > 0 ) {
		return '<div class="notice notice-warning inline"><p>Already seeded — '
			. number_format( $existing ) . ' marker rows found. Run <strong>Cleanup</strong> first to re-seed.</p></div>';
	}

	$start = microtime( true );
	$now   = current_time( 'mysql' );

	$post_values = array();
	for ( $i = 1; $i <= BENCH_POST_COUNT; $i++ ) {
		$post_values[] = $wpdb->prepare(
			'(%s,%s,%s,%s,%s,%s,%s,%s)',
			$now,
			$now,
			'Benchmark post ' . $i,
			'Benchmark Post ' . $i,
			'publish',
			'post',
			$now,
			BENCH_MARKER
		);
		if ( count( $post_values ) >= BENCH_BATCH_SIZE || $i === BENCH_POST_COUNT ) {
			$wpdb->query(
				"INSERT INTO {$wpdb->posts}
				(post_date,post_date_gmt,post_content,post_title,post_status,post_type,post_modified,post_mime_type)
				VALUES " . implode( ',', $post_values )
			);
			$post_values = array();
		}
	}

	$post_ids = $wpdb->get_col(
		$wpdb->prepare( "SELECT ID FROM {$wpdb->posts} WHERE post_mime_type = %s", BENCH_MARKER )
	);

	$meta_keys   = array_slice(
		array(
			'_thumbnail_id',
			'_wp_page_template',
			'_wp_attached_file',
			'_wp_attachment_metadata',
			'_edit_lock',
			'_edit_last',
			'_imagify_status',
			'_imagify_data',
			'_price',
			'_sku',
			'_stock_status',
			'_visibility',
			'_product_attributes',
			'_custom_field',
		),
		0,
		BENCH_META_PER_POST - 1
	);
	$meta_keys[] = BENCH_MARKER;

	$meta_values = array();
	$meta_count  = 0;
	foreach ( $post_ids as $post_id ) {
		foreach ( $meta_keys as $key ) {
			$meta_values[] = $wpdb->prepare( '(%d,%s,%s)', $post_id, $key, 'value_' . $post_id );
			++$meta_count;
			if ( count( $meta_values ) >= BENCH_BATCH_SIZE ) {
				$wpdb->query(
					"INSERT INTO {$wpdb->postmeta} (post_id,meta_key,meta_value) VALUES "
					. implode( ',', $meta_values )
				);
				$meta_values = array();
			}
		}
	}
	if ( $meta_values ) {
		$wpdb->query(
			"INSERT INTO {$wpdb->postmeta} (post_id,meta_key,meta_value) VALUES "
			. implode( ',', $meta_values )
		);
	}

	return '<div class="notice notice-success inline"><p>&#10003; Seeding complete</p></div>'
		. bench_45354_kv_table(
			array(
				'Posts inserted'     => number_format( count( $post_ids ) ),
				'Meta rows inserted' => number_format( $meta_count ),
				'Time'               => round( microtime( true ) - $start, 3 ) . 's',
			)
		);
}

// ─── Benchmark ────────────────────────────────────────────────────────────────

function bench_45354_do_run() {
	global $wpdb;

	$sample_id = (int) $wpdb->get_var(
		$wpdb->prepare( "SELECT post_id FROM {$wpdb->postmeta} WHERE meta_key = %s LIMIT 1", BENCH_MARKER )
	);
	if ( ! $sample_id ) {
		return '<div class="notice notice-error inline"><p>No seeded data found. Run <strong>Seed data</strong> first.</p></div>';
	}

	$queries = array(
		'meta_key only — WHERE meta_key = X'
			=> "SELECT post_id FROM {$wpdb->postmeta}
			    WHERE meta_key = '_thumbnail_id'",

		'meta_key + post_id — WHERE meta_key = X AND post_id = Y'
			=> $wpdb->prepare(
				"SELECT meta_value FROM {$wpdb->postmeta}
				 WHERE meta_key = '_price' AND post_id = %d",
				$sample_id
			),

		'JOIN — posts INNER JOIN postmeta on meta_key'
			=> "SELECT pm.post_id FROM {$wpdb->postmeta} pm
			    INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id
			    WHERE pm.meta_key = '_thumbnail_id' AND p.post_status = 'publish'",

		'Double JOIN — two meta_key filters on same post (common plugin pattern)'
			=> "SELECT p.ID FROM {$wpdb->posts} p
			    INNER JOIN {$wpdb->postmeta} pm1 ON p.ID = pm1.post_id AND pm1.meta_key = '_imagify_status'
			    INNER JOIN {$wpdb->postmeta} pm2 ON p.ID = pm2.post_id AND pm2.meta_key = '_thumbnail_id'
			    WHERE p.post_status = 'publish'",
	);

	$timings = array();
	foreach ( $queries as $label => $sql ) {
		$wpdb->query( 'FLUSH TABLES' ); // Clear table cache before each query batch.
		$times = array();
		for ( $i = 0; $i < BENCH_ITERATIONS; $i++ ) {
			$t = microtime( true );
			$wpdb->get_results( $sql );
			$times[] = microtime( true ) - $t;
		}
		$timings[ $label ] = round( ( array_sum( $times ) / count( $times ) ) * 1000, 4 );
	}

	// Rebuild the table so InnoDB reports accurate per-index sizes.
	$wpdb->query( "OPTIMIZE TABLE {$wpdb->postmeta}" );
	$status       = $wpdb->get_row( $wpdb->prepare( 'SHOW TABLE STATUS LIKE %s', $wpdb->postmeta ) );
	$data_length  = $status ? (int) $status->Data_length : 0;
	$index_length = $status ? (int) $status->Index_length : 0;

	// Active indexes.
	$indexes     = $wpdb->get_results( "SHOW INDEX FROM {$wpdb->postmeta}" );
	$index_names = array_unique( array_column( $indexes, 'Key_name' ) );
	$total_rows  = number_format( (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->postmeta}" ) );

	$html = '<p style="color:#666;font-size:13px;margin-bottom:1.5em">'
		. $total_rows . ' rows &nbsp;&middot;&nbsp; '
		. BENCH_ITERATIONS . ' iterations per query &nbsp;&middot;&nbsp; '
		. 'Indexes on wp_postmeta: <strong>' . esc_html( implode( ', ', $index_names ) ) . '</strong>'
		. '</p>';

	$html .= '<h2>Query timings</h2>';
	$html .= '<table class="widefat striped" style="max-width:640px">'
		. '<thead><tr><th>Query</th><th style="width:160px">Avg time (ms)</th></tr></thead><tbody>';
	foreach ( $timings as $label => $ms ) {
		$html .= '<tr><td>' . esc_html( $label ) . '</td>'
			. '<td><strong>' . esc_html( $ms ) . '</strong></td></tr>';
	}
	$html .= '</tbody></table>';

	// Per-index sizes from InnoDB internal stats (accurate, updated by OPTIMIZE TABLE).
	$page_size   = (int) $wpdb->get_var( 'SELECT @@innodb_page_size' );
	$index_stats = $wpdb->get_results(
		$wpdb->prepare(
			"SELECT index_name, stat_value * %d AS size_bytes
			 FROM mysql.innodb_index_stats
			 WHERE database_name = DATABASE()
			 AND table_name = %s
			 AND stat_name = 'size'
			 ORDER BY index_name",
			$page_size,
			$wpdb->base_prefix . 'postmeta'
		)
	);

	$secondary_total = 0;
	foreach ( $index_stats as $row ) {
		if ( 'PRIMARY' !== $row->index_name ) {
			$secondary_total += (int) $row->size_bytes;
		}
	}

	$html .= '<h2 style="margin-top:1.8em">Table size — wp_postmeta</h2>';
	$html .= '<table class="widefat striped" style="max-width:320px"><tbody>'
		. '<tr><td>Data size</td><td><strong>' . bench_45354_bytes( $data_length ) . '</strong></td></tr>'
		. '<tr><td>Index size (excl. PRIMARY)</td><td><strong>' . bench_45354_bytes( $secondary_total ) . '</strong></td></tr>'
		. '<tr><td>Total size</td><td><strong>' . bench_45354_bytes( $data_length + $secondary_total ) . '</strong></td></tr>'
		. '</tbody></table>';

	if ( $index_stats ) {
		$html .= '<h3 style="margin-top:1.2em">Size per index</h3>';
		$html .= '<table class="widefat striped" style="max-width:320px"><thead>'
			. '<tr><th>Index</th><th>Size</th></tr></thead><tbody>';
		foreach ( $index_stats as $row ) {
			$html .= '<tr><td>' . esc_html( $row->index_name ) . '</td>'
				. '<td><strong>' . bench_45354_bytes( (int) $row->size_bytes ) . '</strong></td></tr>';
		}
		$html .= '</tbody></table>';
	}

	return $html;
}

// ─── Apply schema ─────────────────────────────────────────────────────────────

function bench_45354_do_schema() {
	global $wpdb;

	require_once ABSPATH . 'wp-admin/includes/upgrade.php';

	$before = array_unique( array_column( $wpdb->get_results( "SHOW INDEX FROM {$wpdb->postmeta}" ), 'Key_name' ) );

	$updates = dbDelta( wp_get_db_schema() );

	$after = array_unique( array_column( $wpdb->get_results( "SHOW INDEX FROM {$wpdb->postmeta}" ), 'Key_name' ) );
	$added = array_diff( $after, $before );

	$rows = array(
		'Indexes before'  => implode( ', ', $before ),
		'Indexes after'   => implode( ', ', $after ),
		'New indexes'     => $added ? implode( ', ', $added ) : '— none added',
		'dbDelta changes' => $updates ? implode( '<br>', array_map( 'esc_html', $updates ) ) : '— no changes',
	);

	$notice = $added
		? '<div class="notice notice-success inline"><p>&#10003; Schema applied — new index added.</p></div>'
		: '<div class="notice notice-warning inline"><p>dbDelta ran but no new indexes were added. The index may already exist or the schema change was not detected.</p></div>';

	$html = $notice . '<table class="widefat striped" style="max-width:560px;margin-top:.8em"><tbody>';
	foreach ( $rows as $label => $value ) {
		$html .= '<tr><td style="width:160px">' . esc_html( $label ) . '</td><td><strong>' . $value . '</strong></td></tr>';
	}
	return $html . '</tbody></table>';
}

// ─── Cleanup ──────────────────────────────────────────────────────────────────

function bench_45354_do_cleanup() {
	global $wpdb;

	$deleted_posts = $wpdb->query(
		$wpdb->prepare( "DELETE FROM {$wpdb->posts} WHERE post_mime_type = %s", BENCH_MARKER )
	);
	$wpdb->query(
		$wpdb->prepare( "DELETE FROM {$wpdb->postmeta} WHERE meta_key = %s", BENCH_MARKER )
	);
	$deleted_meta = $wpdb->query(
		"DELETE pm FROM {$wpdb->postmeta} pm
		 LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id
		 WHERE p.ID IS NULL"
	);

	return '<div class="notice notice-success inline"><p>&#10003; Cleanup complete</p></div>'
		. bench_45354_kv_table(
			array(
				'Posts removed'     => number_format( $deleted_posts ),
				'Meta rows removed' => number_format( $deleted_meta ),
			)
		);
}

// ─── Helpers ──────────────────────────────────────────────────────────────────

function bench_45354_kv_table( array $rows ): string {
	$html = '<table class="widefat striped" style="max-width:320px;margin-top:.8em"><tbody>';
	foreach ( $rows as $label => $value ) {
		$html .= '<tr><td>' . esc_html( $label ) . '</td>'
			. '<td><strong>' . esc_html( $value ) . '</strong></td></tr>';
	}
	return $html . '</tbody></table>';
}

function bench_45354_bytes( $bytes ): string {
	$bytes = (int) $bytes;
	if ( $bytes >= 1048576 ) {
		return round( $bytes / 1048576, 2 ) . ' MB';
	}
	if ( $bytes >= 1024 ) {
		return round( $bytes / 1024, 2 ) . ' KB';
	}
	return $bytes . ' B';
}