1 | <?php |
---|
2 | |
---|
3 | $link = mysql_connect( 'localhost', 'root', '' ); |
---|
4 | mysql_select_db( 'test', $link ); |
---|
5 | |
---|
6 | // $engines = array( 'myisam', 'innodb' ); |
---|
7 | $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. |
---|
8 | // $table_sizes = array(100, 1000, 10000, 100000, 1000000); |
---|
9 | $table_sizes = array(100, 1000, 10000, 100000); |
---|
10 | $percentages = array( 50, 20, 5 ); |
---|
11 | // $schemas = array("( a VARCHAR(255), b LONGTEXT, c VARCHAR(20) )", "( a VARCHAR(255), b LONGTEXT, c BOOLEAN )"); |
---|
12 | $schemas = array("( a VARCHAR(255), b LONGTEXT, c VARCHAR(20) )", "( a VARCHAR(255), b LONGTEXT, c VARCHAR(20), INDEX autoload_index (c) )"); |
---|
13 | // $schema_names = array("VARCHAR20", "BOOLEAN"); |
---|
14 | $schema_names = array("VARCHAR20", "VARCHAR20_INDEXED"); |
---|
15 | // $schema_vals = array(array("'NO'", "'YES'"), array("FALSE", "TRUE")); |
---|
16 | $schema_vals = array(array("'NO'", "'YES'"), array("'NO'", "'YES'")); |
---|
17 | $a = str_repeat( 'x', 100 ); |
---|
18 | $b = str_repeat( 'x', 1000 ); |
---|
19 | |
---|
20 | $run_prep_data = TRUE; |
---|
21 | $run_tests = TRUE; |
---|
22 | |
---|
23 | foreach($engines as $key => $engine) { |
---|
24 | foreach($table_sizes as $key => $table_size) { |
---|
25 | foreach($percentages as $key => $percentage) { |
---|
26 | $schema_results = array(); |
---|
27 | $last_avg = -1; |
---|
28 | foreach($schemas as $schema_key => $schema) { |
---|
29 | $schema_name = $schema_names[$schema_key]; |
---|
30 | $schema_cols = $schemas[$schema_key]; |
---|
31 | $schema_val = $schema_vals[$schema_key]; |
---|
32 | $table_name = "{$engine}_{$schema_name}_Size_{$table_size}_Percent_{$percentage}"; |
---|
33 | if ($run_prep_data == TRUE) { |
---|
34 | // Creates |
---|
35 | $setup_sql = "DROP TABLE IF EXISTS {$table_name}; "; |
---|
36 | mysql_query($setup_sql, $link); |
---|
37 | $setup_sql = "CREATE TABLE ${table_name} {$schema_cols} ENGINE={$engine}; "; |
---|
38 | mysql_query($setup_sql, $link); |
---|
39 | |
---|
40 | // Fills |
---|
41 | $batch = 0; |
---|
42 | $sql = "INSERT INTO {$table_name} VALUES "; |
---|
43 | for($row = 0; $row < $table_size; $row++) { |
---|
44 | if ($batch == 100) { |
---|
45 | mysql_query($sql . ";", $link); |
---|
46 | $batch = 0; |
---|
47 | $sql = "INSERT INTO {$table_name} VALUES "; |
---|
48 | } |
---|
49 | if ($batch > 0) $sql .= ", "; |
---|
50 | $c = $schema_val[0]; |
---|
51 | if ( rand( 0, 99 ) < $percentage ) { |
---|
52 | $c = $schema_val[1]; |
---|
53 | } |
---|
54 | $sql .= "('{$a}', '{$b}', $c)"; |
---|
55 | $batch++; |
---|
56 | } |
---|
57 | mysql_query($sql . ";", $link); |
---|
58 | } |
---|
59 | if ($run_tests == TRUE) { |
---|
60 | $i = 1; // We only really are concerned with checking c=YES as in the application we only have that query. |
---|
61 | $select_val = $schema_val[$i]; |
---|
62 | $time_taken = 0; |
---|
63 | for($t = 0; $t < 6; $t++) { |
---|
64 | $sql = "SELECT * FROM {$table_name} WHERE c={$select_val};"; |
---|
65 | // echo "$sql\n"; |
---|
66 | $start = microtime( true ); |
---|
67 | |
---|
68 | $res = mysql_query( $sql, $link ); |
---|
69 | $end = microtime( true ); |
---|
70 | |
---|
71 | mysql_free_result( $res ); |
---|
72 | if ($t > 0) $time_taken = $time_taken + ($end - $start); |
---|
73 | } |
---|
74 | $avg_time = $time_taken / 5; |
---|
75 | $schema_results[$schema_key] = $time_taken / 5; |
---|
76 | echo "{$table_name} {$select_val} {$avg_time}\n"; |
---|
77 | if ($last_avg > -1) { |
---|
78 | if ($last_avg > $avg_time) echo ">> Faster\n\n"; |
---|
79 | else echo ">> Slower\n\n"; |
---|
80 | } |
---|
81 | $last_avg = $avg_time; |
---|
82 | } |
---|
83 | } |
---|
84 | } |
---|
85 | } |
---|
86 | } |
---|