<?php

$link = mysql_connect( 'localhost', 'root', 'root' );
mysql_select_db( 'test', $link );

$tables = array(
	'test100'  => 100,
	'test1k'   => 1000,
	'test10k'  => 10000,
	'test100k' => 100000,
	'test1m'   => 1000000
);

$engines = array( 'innodb', 'myisam' );
$percentages = array( 50, 20, 5 );

// a is like option_name, b is like option_value, c is like autoload
$cols = "( a VARCHAR(255), b LONGTEXT, c VARCHAR(20) )";

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

$previous_table = '';

foreach ( $tables as $table => $rows ) {
	echo "Creating $table...\n";

	mysql_query( "DROP TABLE IF EXISTS {$table}_innodb_50" );
	mysql_query( "DROP TABLE IF EXISTS {$table}_innodb_20" );
	mysql_query( "DROP TABLE IF EXISTS {$table}_innodb_5" );

	mysql_query( "DROP TABLE IF EXISTS {$table}_myisam_50" );
	mysql_query( "DROP TABLE IF EXISTS {$table}_myisam_20" );
	mysql_query( "DROP TABLE IF EXISTS {$table}_myisam_5" );

	mysql_query( "CREATE TABLE {$table}_innodb_50 $cols ENGINE=InnoDB;" );
	mysql_query( "CREATE TABLE {$table}_innodb_20 $cols ENGINE=InnoDB;" );
	mysql_query( "CREATE TABLE {$table}_innodb_5 $cols ENGINE=InnoDB;" );

	mysql_query( "CREATE TABLE {$table}_myisam_50 $cols ENGINE=MyISAM;" );
	mysql_query( "CREATE TABLE {$table}_myisam_20 $cols ENGINE=MyISAM;" );
	mysql_query( "CREATE TABLE {$table}_myisam_5 $cols ENGINE=MyISAM;" );

	
	echo "Filling tables";

	if ( $rows < 100000 ) {
		for ( $ii = 0; $ii < $rows; $ii++ ) {
			$val = "no";
			if ( rand( 0, 99 ) < 50 ) {
				$val = "yes";
			}
			mysql_query( "INSERT INTO {$table}_innodb_50 VALUES('$a', '$b', '$val');" );
	
			$val = "no";
			if ( rand( 0, 99 ) < 20 ) {
				$val = "yes";
			}
			mysql_query( "INSERT INTO {$table}_innodb_20 VALUES('$a', '$b', '$val');" );
	
			$val = "no";
			if ( rand( 0, 99 ) < 5 ) {
				$val = "yes";
			}
			mysql_query( "INSERT INTO {$table}_innodb_5 VALUES('$a', '$b', '$val');" );
	
			if ( $ii && ! ( $ii % ( $rows / 10 ) ) ) {
				echo '.';
			}
		}
	} else {
		// For the huge tables, it takes way too long to generate random rows,
		// so we approximate it by taking multiple copies of previously
		// generated tables, instead.
		for ( $ii = 0; $ii < $rows; $ii += $rows/10 ) {
			mysql_query( "INSERT INTO {$table}_innodb_50 SELECT * FROM {$previous_table}_innodb_50;" );
			mysql_query( "INSERT INTO {$table}_innodb_20 SELECT * FROM {$previous_table}_innodb_20;" );
			mysql_query( "INSERT INTO {$table}_innodb_5 SELECT * FROM {$previous_table}_innodb_5;" );
			echo '.';
		}
	}
	
	echo "\nCopying tables...\n";
	mysql_query( "INSERT INTO {$table}_myisam_50 SELECT * FROM {$table}_innodb_50;" );
	mysql_query( "INSERT INTO {$table}_myisam_20 SELECT * FROM {$table}_innodb_20;" );
	mysql_query( "INSERT INTO {$table}_myisam_5 SELECT * FROM {$table}_innodb_5;" );

	$previous_table = $table;
}

$times = array();
foreach ( $tables as $table => $row ) {
	$times[ $table ] = array();
	foreach ( $engines as $engine ) {
		$times[ $table ][ $engine ] = array();
		foreach( $percentages as $percentage ) {
			$times[ $table ][ $engine ][ $percentage ] = array();
			$times[ $table ][ $engine ][ $percentage ]['yes'] = array();
			$times[ $table ][ $engine ][ $percentage ]['no'] = array();
		
			for ( $ii = 0; $ii < 5; $ii++ ) {
				$start = microtime( true );
				$res = mysql_query( "SELECT * FROM {$table}_{$engine}_{$percentage} WHERE c='yes';" );
				$end = microtime( true );
				
				mysql_free_result( $res );
				
				// Ignore the first result, it was taken while the buffer pool was warming up
				if ( $ii ) {
					$times[ $table ][ $engine ][ $percentage ]['yes'][] = $end - $start;
				}

				$start = microtime( true );
				$res = mysql_query( "SELECT * FROM {$table}_{$engine}_{$percentage} WHERE c='no';" );
				$end = microtime( true );
				
				mysql_free_result( $res );
				
				if ( $ii ) {
					$times[ $table ][ $engine ][ $percentage ]['no'][] = $end - $start;
				}
			}

			$avg = array_sum( $times[ $table ][ $engine ][ $percentage ]['yes'] ) / count( $times[ $table ][ $engine ][ $percentage ]['yes'] );
			echo "{$table}_{$engine}_{$percentage} ('yes'): $avg\n";

			$avg = array_sum( $times[ $table ][ $engine ][ $percentage ]['no'] ) / count( $times[ $table ][ $engine ][ $percentage ]['no'] );
			echo "{$table}_{$engine}_{$percentage} ('no'): $avg\n";
		}
	}			
}

$times_indexed = array();
foreach ( $tables as $table => $row ) {
	$times_indexed[ $table ] = array();
	foreach ( $engines as $engine ) {
		$times_indexed[ $table ][ $engine ] = array();
		foreach( $percentages as $percentage ) {
			$times_indexed[ $table ][ $engine ][ $percentage ] = array();
			$times_indexed[ $table ][ $engine ][ $percentage ]['yes'] = array();
			$times_indexed[ $table ][ $engine ][ $percentage ]['no'] = array();
		
			mysql_query( "ALTER TABLE {$table}_{$engine}_{$percentage} ADD INDEX (c);" );

			for ( $ii = 0; $ii < 5; $ii++ ) {
				$start = microtime( true );
				$res = mysql_query( "SELECT * FROM {$table}_{$engine}_{$percentage} WHERE c='yes';" );
				$end = microtime( true );

				mysql_free_result( $res );
				
				if ( $ii ) {
					$times_indexed[ $table ][ $engine ][ $percentage ]['yes'][] = $end - $start;
				}

				$start = microtime( true );
				$res = mysql_query( "SELECT * FROM {$table}_{$engine}_{$percentage} WHERE c='no';" );
				$end = microtime( true );
				
				mysql_free_result( $res );
				
				if ( $ii ) {
					$times_indexed[ $table ][ $engine ][ $percentage ]['no'][] = $end - $start;
				}
			}

			$avg = array_sum( $times_indexed[ $table ][ $engine ][ $percentage ]['yes'] ) / count( $times_indexed[ $table ][ $engine ][ $percentage ]['yes'] );
			echo "{$table}_{$engine}_{$percentage} indexed ('yes'): $avg";

			if ( $avg > ( array_sum( $times[ $table ][ $engine ][ $percentage ]['yes'] ) / count( $times[ $table ][ $engine ][ $percentage ]['yes'] ) ) ) {
				echo " (slower)\n";
			} else {
				echo " (faster)\n";
			}

			$avg = array_sum( $times_indexed[ $table ][ $engine ][ $percentage ]['no'] ) / count( $times_indexed[ $table ][ $engine ][ $percentage ]['no'] );
			echo "{$table}_{$engine}_{$percentage} indexed ('no'): $avg";

			if ( $avg > ( array_sum( $times[ $table ][ $engine ][ $percentage ]['no'] ) / count( $times[ $table ][ $engine ][ $percentage ]['no'] ) ) ) {
				echo " (slower)\n";
			} else {
				echo " (faster)\n";
			}
		}
	}			
}