SQL .

SQL MySQL Benchmark Tuning With PHP

I recently watched an excellent video titled "Performance Tuning Best Practices for MySQL" with Jay Pipes. This video made me consider some of the haphazard methodologies I'd been using to tune my own MySQL databases. One practice Mr. Pipes stressed was to do benchmark testing, and keep good records of these tests and results. And so, I've set up this simple module to allow me to run multiple benchmark queries and compare their times, giving me a nice formatted output which can easily be saved in a document. This module will also do an Explain Extended of each query, and even allow a bit of sql to run before each query - useful if you want to try the same query with different indexes.

The query I'm testing here is one that I have actually been working with for a page on my now defunct numerology site. My page on that site listing Surnames Starting with the Letter A did a query against the surname table looking for the top 20 most popular ranking surnames starting with the letter a. There are about 145,000 records in that table, so it's something that needs to be properly indexed to get top performance. I had already tuned the query a bit to allow only names in the top 2,000 rank to be considered, which did help immensely. However, I want to now test this query with no index, with an index on the first character of surname, and with an index on the first two characters of surname.

Here is the module to test that scenario:

//Copyright www.LarryTruett.com and www.FluffyCat.com August 17, 2009  
  $db_connection = db_connect();
  mysql_select_db (db_name()) or exit();
    
  set_time_limit (500);    
  
  echo '<h1>MySQL Benchmark Tuning Test '.date("Y-m-d H:i:s").'</h1>';

$queries = array(); $prequeries = array(); $titles = array(); $queries[] = "SELECT * FROM surname WHERE name like 'A%' AND rank < 2000 ORDER BY rank LIMIT 20"; $queries[] = "SELECT * FROM surname WHERE name like 'A%' AND rank < 2000 ORDER BY rank LIMIT 20"; $queries[] = "SELECT * FROM surname WHERE name like 'A%' AND rank < 2000 ORDER BY rank LIMIT 20"; $prequeries[] = NULL; $prequeries[] = "ALTER TABLE `surname` ADD INDEX `index_name1_rank` ( `name` ( 1 ) , `rank` )"; $prequeries[] = "ALTER TABLE `surname` DROP INDEX `index_name1_rank` , ADD INDEX `index_name2_rank` ( `name` ( 2 ) , `rank` )"; $titles[] = 'With No Index'; $titles[] = 'With Index on first char of name and rank'; $titles[] = 'With Index on first 2 chars of name and rank';

benchmark($queries,$prequeries,$titles); function benchmark($queries,$prequeries,$titles) { $count = 0; foreach ($queries as $query) { echo '<h2>TEST '.($count+1).' - '.$titles[$count].'</h2>'; echo 'QUERY: '.$query.'<br><br>'; //if there is a prequery, such as adding an index, run that first if (NULL != $prequeries[$count]) { $prequery = $prequeries[$count]; echo 'PREQUERY: '.$prequery.'<br><br>'; $result = mysql_query($prequery) or die("prequery not successful:".mysql_error()); } $equery = 'EXPLAIN EXTENDED '.$query; $result = mysql_query($equery) or die("equery not successful:".mysql_error()); echo '<h3>EXPLAIN EXTENDED</h3>'; while ($line = mysql_fetch_array($result)) { echo 'ID: '.$line[0].'<br>'; echo 'SELECT TYPE: '.$line[1].'<br>'; echo 'TABLE: '.$line[2].'<br>'; echo 'TYPE: '.$line[3].'<br>'; echo 'POSSIBLE KEYS: '.$line[4].'<br>'; echo 'KEY USED: '.$line[5].'<br>'; echo 'KEY LENGTH: '.$line[6].'<br>'; echo 'REF: '.$line[7].'<br>'; echo 'ROWS: '.$line[8].'<br>'; echo 'EXTRA: '.$line[9].'<br><br>'; } $time_start = microtime(true); $iterations = 1000; for ($x = 0; $x < $iterations; $x++) { $result = mysql_query($query) or die("query not successful: ".mysql_error()); }

$time_end = microtime(true); $total_time = $time_end - $time_start; echo '*******************************'.'<br>'; echo 'Total Time for '.$iterations.' iterations: '.$total_time.'<br>'; echo 'Total Time per iteration: '.($total_time/$iterations).'<br>'; echo '*******************************'.'<br>'; echo '<br><br>'; $count++; } }

function db_name() { return ("your_db_name_here"); } function db_connect() { $db_connection = mysql_connect("localhost", "your_mysql_id_here", "your_mysql_pw_here"); return $db_connection; }


And here are the results from my test:

MySQL Benchmark Tuning Test 2009-08-17 10:09:09

TEST 1 - With No Index

QUERY: SELECT * FROM surname WHERE name like 'A%' AND rank < 2000 ORDER BY rank LIMIT 20

EXPLAIN EXTENDED

ID: 1 SELECT TYPE: SIMPLE TABLE: surname TYPE: range POSSIBLE KEYS: index_name,index_rank KEY USED: index_rank KEY LENGTH: 5 REF: ROWS: 1995 EXTRA: Using where

******************************* Total Time for 1000 iterations: 3.940691947937 Total Time per iteration: 0.003940691947937 *******************************

TEST 2 - With Index on first char of name and rank

QUERY: SELECT * FROM surname WHERE name like 'A%' AND rank < 2000 ORDER BY rank LIMIT 20

PREQUERY: ALTER TABLE `surname` ADD INDEX `index_name1_rank` ( `name` ( 1 ) , `rank` )

EXPLAIN EXTENDED

ID: 1 SELECT TYPE: SIMPLE TABLE: surname TYPE: range POSSIBLE KEYS: index_name,index_rank,index_name1_rank KEY USED: index_name1_rank KEY LENGTH: 9 REF: ROWS: 66 EXTRA: Using where; Using filesort

******************************* Total Time for 1000 iterations: 0.91901803016663 Total Time per iteration: 0.00091901803016663 *******************************

TEST 3 - With Index on first 2 chars of name and rank

QUERY: SELECT * FROM surname WHERE name like 'A%' AND rank < 2000 ORDER BY rank LIMIT 20

PREQUERY: ALTER TABLE `surname` DROP INDEX `index_name1_rank` , ADD INDEX `index_name2_rank` ( `name` ( 2 ) , `rank` )

EXPLAIN EXTENDED

ID: 1 SELECT TYPE: SIMPLE TABLE: surname TYPE: range POSSIBLE KEYS: index_name,index_rank,index_name2_rank KEY USED: index_rank KEY LENGTH: 5 REF: ROWS: 1995 EXTRA: Using where

******************************* Total Time for 1000 iterations: 4.0065231323242 Total Time per iteration: 0.0040065231323242 *******************************


This proves that an index on only the first letter of surname is a clear winner over no index at all. Surprisingly (to me) is the fact that the index on the first two letters is actually a bit slower than no index at all. So, that's clear proof that you want to set up your indexes to match the size of the string being searched on whenever possible.

Obviously, you can use this module to test all sorts of scenarios - not just 3 different indexing strategies. You could try variations of a query - or perhaps all of the queries that run to build one problematic page to see which one is really eating up time.
Comments Comments are left by visitors to FluffyCat.com and may or may not be accurate.
Comment by Larry on 2009-08-17 Rate this Comment

Note: for this test I build two different indexes, which must not be there when before the test is run. Initially I had the first prequery dropping both indexes. It turns out that you can't drop an index if it does not exist - there is no "IF EXISTS" parameter for indexes. So, you just have to start off by making sure that any indexes you will be adding are not there when testing starts.

 
Sign in to comment on SQL MySQL Benchmark Tuning With PHP.