<?php

$connections = array(
		array(
			'host' => 'localhost',
			'port' => '3306',
			'user' => 'root',
			'pass' => 'root',
		),
);

$engines = array( 'myisam', 'innodb' );
$row_counts = array( 100000, 1000000, 10000000 );
$percent_underscores = array( 10, 30, 60, 90 );
$unique_keys_count = array( 10, 100, 1000, 10000 );

$queries = array(
	'original' => "SELECT meta_key
		FROM wp_postmeta
		GROUP BY meta_key
		HAVING meta_key NOT LIKE '\_%'
		ORDER BY meta_key
		LIMIT 30",
	'range' => "SELECT DISTINCT meta_key FROM wp_postmeta
		WHERE meta_key NOT BETWEEN '_' AND '_z'
		HAVING meta_key NOT LIKE '\_%'
		ORDER BY meta_key",
	'notlike' => "SELECT DISTINCT meta_key
		FROM wp_postmeta
		WHERE meta_key NOT LIKE '\_%'
		ORDER BY meta_key
		LIMIT 30"
);

foreach ( $connections as $connection ) {
	$dbh = new mysqli( $connection['host'], $connection['user'], $connection['pass'], null, $connection['port'] );
	if ( $dbh->connect_error ) {
		die( 'Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error );
	}

	foreach( $engines as $engine ) {
		$dbh->query( "DROP DATABASE IF EXISTS 24498_test" );
		$dbh->query( "CREATE DATABASE 24498_test" );
		$dbh->query( "USE 24498_test" );
		$dbh->query( "CREATE TABLE wp_postmeta (
			id BIGINT UNSIGNED AUTO_INCREMENT,
			meta_key varchar(255),
			PRIMARY KEY(id),
			KEY meta_key(meta_key(191))
		) ENGINE=$engine" );

		$results = array();
		foreach( $row_counts as $rows ) {
			$results[ $rows ] = array();
			foreach( $percent_underscores as $percent_underscore ) {
				$results[ $rows ][ $percent_underscore ] = array();
				foreach( $unique_keys_count as $unique_keys ) {
					$results[ $rows ][ $percent_underscore ][ $unique_keys ] = array();
					$keys = array();
					for( $ii = 0; $ii < $unique_keys; $ii++ ) {
						$keys[] = substr( str_shuffle( md5( microtime( true ) ) ), 0, 10 );
					}

					$values = array();
					for( $ii = 0; $ii < $rows; $ii++ ) {
						$underscore = rand( 0, 100 ) < $percent_underscore ? '_' : '';
						$key = $underscore . $keys[ rand( 0, $unique_keys - 1 ) ];
						$values[] = $key;
					}
					$values_string = "('" . implode( "'),('", $values ) . "')";
					$dbh->query( "INSERT INTO wp_postmeta (meta_key) VALUES $values_string" );

					foreach( $queries as $name => $query ) {
						$results[ $rows ][ $percent_underscore ][ $unique_keys ][ $name ] = array();
						for( $ii = 0; $ii < 100; $ii++ ) {
							$now = microtime( true );
							$dbh->query( $query );
							$results[ $rows ][ $percent_underscore ][ $unique_keys ][ $name ][] = microtime( true ) - $now;
						}
						rsort( $results[ $rows ][ $percent_underscore ][ $unique_keys ][ $name ] );
						echo "$engine, $rows, $percent_underscore, $unique_keys, $name: " . $results[ $rows ][ $percent_underscore ][ $unique_keys ][ $name ][49] . "\n";
					}

					$dbh->query( "TRUNCATE TABLE wp_postmeta" );
				}
			}
		}
	}
}