Ticket #24044: 24044-test.php

<?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";
                        }
                }
        }                       
}