Ticket #24044: wp_options_test_index_options.php

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