<?php/*Quick and dirty bench script showing quirk/bug/performance issue with mysql 5.0 where arbitrarily selecting an extra blob field gives a 22.5% performance boost to the query.The script will create, drop and benchmark test data on request.This is a reduction of a more complex 5 table query that gained a 44.5% performance boost from the added blob, but some of this has been lost from the simplification.Performance gains are lost for each field removed from the query.Ordering by anything other than the datetime will result in the query being slower than without the blob (ie as expected).Simplified Query:SELECT id, bid, cid, enum1, bool1, bool2, bool3, magic_blob # unrequired null blob increases performance FROM mquirk_table1 WHERE bool3 = 1 ORDER BY stamp DESC LIMIT 0, 100Any explanations, comments, feedback welcome :)- L0cky*/$host = 'localhost';$user = 'root';$pass = 'your_pass';$port = 3307; //default 3306$db = 'your_db';set_time_limit(300);if(isset($_POST['setup']) && $_POST['setup']){ $dbcnx = mysqli_connect($host, $user, $pass, false, $port); mysqli_select_db($dbcnx, $db); $sql = "SHOW TABLES FROM $db LIKE 'mquirk_table%'"; $result = mysqli_query($dbcnx, $sql); if(mysqli_num_rows($result)) { echo('Test data already exists'); } else { $sql = "CREATE TABLE IF NOT EXISTS `mquirk_table1` ( `id` int(11) NOT NULL auto_increment, `bid` int(11) NOT NULL, `cid` int(11) NOT NULL, `enum1` ENUM('VAL1', 'VAL2', 'VAL3') default 'VAL3', `bool1` tinyint(1) default 0, `bool2` tinyint(1) default 0, `bool3` tinyint(1) default 0, `stamp` datetime, `magic_blob` blob, PRIMARY KEY (`id`) ) ENGINE=MyISAM"; mysqli_query($dbcnx, $sql) or die(mysqli_error($dbcnx)); for($x=0;$x<100000;$x++) { $bid = rand(1, 10000); $cid = rand(1, 10000); $sql = "INSERT INTO `mquirk_table1` SET bid='$bid',cid='$cid',enum1='VAL3',bool1=0,bool2=0,bool3=1,stamp=NOW()"; mysqli_query($dbcnx, $sql) or die(mysqli_error($dbcnx)); } echo('Test table created, ready for benchmarking<br /><br /><br />'); }}else if(isset($_POST['unsetup']) && $_POST['unsetup']){ $dbcnx = mysqli_connect($host, $user, $pass, false, $port); mysqli_select_db($dbcnx, $db); $sql = 'DROP TABLE IF EXISTS `mquirk_table1`'; mysqli_query($dbcnx, $sql) or die(mysqli_error($dbcnx)); echo('Test table dropped.<br /><br /><br />');}else if(isset($_POST['benchmark']) && $_POST['benchmark']){ $dbcnx = mysqli_connect($host, $user, $pass, false, $port); mysqli_select_db($dbcnx, $db); set_time_limit(6000); $time = microtime(true); for($x=0;$x<100;$x++) { $sql = 'SELECT id, bid, cid, enum1, bool1, bool2, bool3, magic_blob # unrequired null blob increases performance FROM mquirk_table1 WHERE bool3 = 1 ORDER BY stamp DESC LIMIT 0, 100'; if(!mysqli_query($dbcnx, $sql)) { die(mysqli_error($dbcnx)); } $sql = 'RESET QUERY CACHE'; if(!mysqli_query($dbcnx, $sql)) { die(mysqli_error($dbcnx)); } } $w_time = (microtime(true) - $time); echo('With blob: ' . (microtime(true) - $time) . '<br />'); $time = microtime(true); for($x=0;$x<100;$x++) { $sql = 'SELECT id, bid, cid, enum1, bool1, bool2, bool3 # no magic blob FROM mquirk_table1 t1 WHERE bool3 = 1 ORDER BY stamp DESC LIMIT 0, 100'; if(!mysqli_query($dbcnx, $sql)) { die(mysqli_error($dbcnx)); } $sql = 'RESET QUERY CACHE'; if(!mysqli_query($dbcnx, $sql)) { die(mysqli_error($dbcnx)); } } $wo_time = (microtime(true) - $time); echo('Without blob: ' . (microtime(true) - $time) . '<br />'); echo('Difference: ' . round((($wo_time - $w_time)/$w_time)*100, 2) . '%<br />');}else{?> Setting up the test data should take no longer than 15 seconds.<br /> Benchmarking Should take no longer than 30 seconds.<br /> Ensure you don't coincidentally have a table called 'mquirk_table1' before dropping it :P - L0cky<br /><?php}?><form action="" method="POST"><input type="submit" name="setup" value="1. Create Test Table" /><input type="submit" name="benchmark" value="2. Benchmark" /><input type="submit" name="unsetup" value="3. Drop Test Table" /></form>