Ticket #24044: dan_test.php

<?php

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

// $engines = array( 'myisam', 'innodb' );
$engines = array( 'innodb' ); // Seeing as how MyISAM isn't really good for any high concurrency read and write, there doesn't seem much point testing it.
// $table_sizes = array(100, 1000, 10000, 100000, 1000000);
$table_sizes = array(100, 1000, 10000, 100000);
$percentages = array( 50, 20, 5 );
// $schemas = array("( a VARCHAR(255), b LONGTEXT, c VARCHAR(20) )", "( a VARCHAR(255), b LONGTEXT, c BOOLEAN )");
$schemas = array("( a VARCHAR(255), b LONGTEXT, c VARCHAR(20) )", "( a VARCHAR(255), b LONGTEXT, c VARCHAR(20), INDEX autoload_index (c) )");
// $schema_names = array("VARCHAR20", "BOOLEAN");
$schema_names = array("VARCHAR20", "VARCHAR20_INDEXED");
// $schema_vals = array(array("'NO'", "'YES'"), array("FALSE", "TRUE"));
$schema_vals = array(array("'NO'", "'YES'"), array("'NO'", "'YES'"));
$a = str_repeat( 'x', 100 );
$b = str_repeat( 'x', 1000 );

$run_prep_data = TRUE;
$run_tests = TRUE;

foreach($engines as $key => $engine) {
  foreach($table_sizes as $key => $table_size) {
    foreach($percentages as $key => $percentage) {
      $schema_results = array();
      $last_avg = -1;
      foreach($schemas as $schema_key => $schema) {
        $schema_name = $schema_names[$schema_key];
        $schema_cols = $schemas[$schema_key];
        $schema_val = $schema_vals[$schema_key];
        $table_name = "{$engine}_{$schema_name}_Size_{$table_size}_Percent_{$percentage}";
        if ($run_prep_data == TRUE) {
          // Creates
          $setup_sql = "DROP TABLE IF EXISTS {$table_name}; ";
          mysql_query($setup_sql, $link);
          $setup_sql = "CREATE TABLE ${table_name} {$schema_cols} ENGINE={$engine}; ";
          mysql_query($setup_sql, $link);
        
          // Fills
          $batch = 0;
          $sql = "INSERT INTO {$table_name} VALUES ";
          for($row = 0; $row < $table_size; $row++) {
            if ($batch == 100) {
              mysql_query($sql . ";", $link);
              $batch = 0;
              $sql = "INSERT INTO {$table_name} VALUES ";
            }
            if ($batch > 0) $sql .= ", ";
            $c = $schema_val[0];
            if ( rand( 0, 99 ) < $percentage ) {
              $c = $schema_val[1];
            }
            $sql .= "('{$a}', '{$b}', $c)";
            $batch++;
          }
          mysql_query($sql . ";", $link);
        }
        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 = 0;
          for($t = 0; $t < 6; $t++) {
            $sql = "SELECT * FROM {$table_name} WHERE c={$select_val};";
            // echo "$sql\n";
            $start = microtime( true );

            $res = mysql_query( $sql, $link );
            $end = microtime( true );

            mysql_free_result( $res );
            if ($t > 0) $time_taken = $time_taken + ($end - $start);
          }
          $avg_time = $time_taken / 5;
          $schema_results[$schema_key] = $time_taken / 5;
          echo "{$table_name} {$select_val} {$avg_time}\n";
          if ($last_avg > -1) {
            if ($last_avg > $avg_time) echo ">> Faster\n\n";
            else echo ">> Slower\n\n";
          }
          $last_avg = $avg_time;
        }
      }
    }
  }
}