<?php

$link = new mysqli( 'localhost', 'root', '', 'test' );

$engines = array(
	'MyISAM',
	'InnoDB',
);

$table_sizes = array( 300, 3000, 30000, 300000 );

$percentages = array( 80, 50, 20, 5 );

$schemas = array(
	'VARCHAR20' => array(
		'def' => "option_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, option_name VARCHAR(191) DEFAULT NULL, option_value LONGTEXT NOT NULL, autoload VARCHAR(20) NOT NULL DEFAULT 'yes', PRIMARY KEY (option_id), UNIQUE KEY option_name (option_name)",
		'values' => array("'no'", "'yes'"),
	),
	'VARCHAR20_idx' => array(
		'def' => "option_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, option_name VARCHAR(191) DEFAULT NULL, option_value LONGTEXT NOT NULL, autoload VARCHAR(20) NOT NULL DEFAULT 'yes', PRIMARY KEY (option_id), UNIQUE KEY option_name (option_name), INDEX autoload_index (autoload)",
		'values' => array("'no'", "'yes'"),
	),
	'ENUM_idx' => array(
		'def' => "option_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, option_name VARCHAR(191) DEFAULT NULL, option_value LONGTEXT NOT NULL, autoload ENUM('yes', 'no') NOT NULL DEFAULT 'yes', PRIMARY KEY (option_id), UNIQUE KEY option_name (option_name), INDEX autoload_index (autoload)",
		'values' => array("'no'", "'yes'"),
	),
	'CHAR1_idx' => array(
		'def' => "option_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, option_name VARCHAR(191) DEFAULT NULL, option_value LONGTEXT NOT NULL, autoload CHAR(1) NOT NULL DEFAULT 'y', PRIMARY KEY (option_id), UNIQUE KEY option_name (option_name), INDEX autoload_index (autoload)",
		'values' => array("'n'", "'y'"),
	),
	'TINYINT_idx' => array(
		'def' => "option_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, option_name VARCHAR(191) DEFAULT NULL, option_value LONGTEXT NOT NULL, autoload TINYINT(1) NOT NULL DEFAULT 0, PRIMARY KEY (option_id), UNIQUE KEY option_name (option_name), INDEX autoload_index (autoload)",
		'values' => array("0", "1"),
	),
	'VARCHAR20_idx2' => array(
		'def' => "option_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, option_name VARCHAR(191) DEFAULT NULL, option_value LONGTEXT NOT NULL, autoload VARCHAR(20) NOT NULL DEFAULT 'yes', PRIMARY KEY (option_id), UNIQUE KEY option_name (option_name), INDEX autoload_index (autoload, option_name)",
		'values' => array("'no'", "'yes'"),
	),
);

$b = str_repeat( 'x', 1000 );

$run_prep_data = FALSE;
$run_tests = TRUE;

foreach ($engines as $key => $engine) {
	foreach ($table_sizes as $key => $table_size) {
		foreach ($percentages as $key => $percentage) {
			$last_avg = FALSE;
			echo "{$engine} - Size {$table_size} - Percent {$percentage}\n";
			foreach ($schemas as $schema_name => $schema) {
				$schema_val = $schema['values'];
				$table_name = "{$engine}_{$schema_name}_Size_{$table_size}_Percent_{$percentage}";

				if ($run_prep_data == TRUE) {
					// Creates
					$setup_sql = "DROP TABLE IF EXISTS {$table_name}; ";
					$link->query($setup_sql) or die(__LINE__ . ': ' . $link->error . PHP_EOL);
					$setup_sql = "CREATE TABLE ${table_name} ({$schema['def']}) ENGINE={$engine} DEFAULT CHARSET=utf8mb4;";
					$link->query($setup_sql) or die(__LINE__ . ': ' . $link->error . PHP_EOL);
				
					// Fills
					$sql = "INSERT INTO {$table_name} (option_name, option_value, autoload) VALUES ";
					$batch = 0;
					$rows = array();
					for ($row = 0; $row < $table_size; $row++) {
						if ($batch == 100) {
							$link->query($sql . implode(', ', $rows)) or die(__LINE__ . ': ' . $link->error . PHP_EOL);
							$batch = 0;
							$rows = array();
						}
						$a = "option_number_{$row}";
						$c = ( ( $row % 100 ) < $percentage ) ? $schema_val[1] : $schema_val[0];
						$rows[] = "('{$a}', '{$b}', $c)";
						$batch++;
					}
					$link->query($sql . implode(', ', $rows)) or die(__LINE__ . ': ' . $link->error . PHP_EOL);
					$rows = array();
				}

				if ($run_tests == TRUE) {
					$i = 1; // We only really are concerned with checking c=yes as in the application we only have that query.
					$select_val = $schema_val[$i];
					$time_taken = array();
					for ($t = 0; $t < 10; $t++) {
						$sql = "SELECT option_name, option_value FROM {$table_name} WHERE autoload = {$select_val};";

						$start = microtime( true );
						$res = $link->query( $sql );
						$end = microtime( true );

						if (!$res) {
							die(__LINE__ . ': ' . $link->error . PHP_EOL);
						}

						$res->free();
						if ($t > 0) {
							$time_taken[] = ($end - $start);
						}
					}

					// Drop the largest and smallest
					sort($time_taken);
					array_shift($time_taken);
					array_pop($time_taken);

					$avg_time = array_sum($time_taken) / count($time_taken);

					// Set the baseline
					if ($last_avg === FALSE) {
						$last_avg = $avg_time;
					}
					// If the difference is significant (more than 10 ms), print it
					elseif (abs($last_avg - $avg_time) > 0.010) {
						echo "{$schema_name} {$select_val} " , number_format($avg_time, 5);

						if ($last_avg < $avg_time) {
							echo " << Slower ";
						}
						else {
							echo " >> Faster ";
						}
						echo number_format(100 * $last_avg / $avg_time, 0) , "%\n";
					}
				}
			}
			echo "\n";
		}
	}
}