Ticket #24498: 24498-test.php

<?php

$connections = array(
                array(
                        'host' => 'localhost',
                        'port' => '3306',
                        'user' => 'root',
                        'pass' => 'root',
                ),
);

$engines = array( 'myisam', 'innodb' );
$row_counts = array( 100000, 1000000, 10000000 );
$percent_underscores = array( 10, 30, 60, 90 );
$unique_keys_count = array( 10, 100, 1000, 10000 );

$queries = array(
        'original' => "SELECT meta_key
                FROM wp_postmeta
                GROUP BY meta_key
                HAVING meta_key NOT LIKE '\_%'
                ORDER BY meta_key
                LIMIT 30",
        'range' => "SELECT DISTINCT meta_key FROM wp_postmeta
                WHERE meta_key NOT BETWEEN '_' AND '_z'
                HAVING meta_key NOT LIKE '\_%'
                ORDER BY meta_key",
        'notlike' => "SELECT DISTINCT meta_key
                FROM wp_postmeta
                WHERE meta_key NOT LIKE '\_%'
                ORDER BY meta_key
                LIMIT 30"
);

foreach ( $connections as $connection ) {
        $dbh = new mysqli( $connection['host'], $connection['user'], $connection['pass'], null, $connection['port'] );
        if ( $dbh->connect_error ) {
                die( 'Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error );
        }

        foreach( $engines as $engine ) {
                $dbh->query( "DROP DATABASE IF EXISTS 24498_test" );
                $dbh->query( "CREATE DATABASE 24498_test" );
                $dbh->query( "USE 24498_test" );
                $dbh->query( "CREATE TABLE wp_postmeta (
                        id BIGINT UNSIGNED AUTO_INCREMENT,
                        meta_key varchar(255),
                        PRIMARY KEY(id),
                        KEY meta_key(meta_key(191))
                ) ENGINE=$engine" );

                $results = array();
                foreach( $row_counts as $rows ) {
                        $results[ $rows ] = array();
                        foreach( $percent_underscores as $percent_underscore ) {
                                $results[ $rows ][ $percent_underscore ] = array();
                                foreach( $unique_keys_count as $unique_keys ) {
                                        $results[ $rows ][ $percent_underscore ][ $unique_keys ] = array();
                                        $keys = array();
                                        for( $ii = 0; $ii < $unique_keys; $ii++ ) {
                                                $keys[] = substr( str_shuffle( md5( microtime( true ) ) ), 0, 10 );
                                        }

                                        $values = array();
                                        for( $ii = 0; $ii < $rows; $ii++ ) {
                                                $underscore = rand( 0, 100 ) < $percent_underscore ? '_' : '';
                                                $key = $underscore . $keys[ rand( 0, $unique_keys - 1 ) ];
                                                $values[] = $key;
                                        }
                                        $values_string = "('" . implode( "'),('", $values ) . "')";
                                        $dbh->query( "INSERT INTO wp_postmeta (meta_key) VALUES $values_string" );

                                        foreach( $queries as $name => $query ) {
                                                $results[ $rows ][ $percent_underscore ][ $unique_keys ][ $name ] = array();
                                                for( $ii = 0; $ii < 100; $ii++ ) {
                                                        $now = microtime( true );
                                                        $dbh->query( $query );
                                                        $results[ $rows ][ $percent_underscore ][ $unique_keys ][ $name ][] = microtime( true ) - $now;
                                                }
                                                rsort( $results[ $rows ][ $percent_underscore ][ $unique_keys ][ $name ] );
                                                echo "$engine, $rows, $percent_underscore, $unique_keys, $name: " . $results[ $rows ][ $percent_underscore ][ $unique_keys ][ $name ][49] . "\n";
                                        }

                                        $dbh->query( "TRUNCATE TABLE wp_postmeta" );
                                }
                        }
                }
        }
}