[TYPO3-dev] Quick speed test mysql vs mysqli, for now a win for mysql

[ries van Twisk]

Hey Guys,

today I did a very quick speedtest for mysql native and mysqli with  
prepared statements.

I filled a simpel table with 1000 records and each records has one  
test field with 1Kbye of random textual content.


THis is the table DDL (MyISAM):

CREATE TABLE `test` (
   `uid` int(11) NOT NULL auto_increment,
   `pid` int(11) NOT NULL,
   `text` text collate utf8_unicode_ci NOT NULL,
   PRIMARY KEY  (`uid`),
   KEY `pid` (`pid`)
) ;


This was how I filled the table

<?php

$connection = mysql_connect('localhost', 'root', '');
mysql_select_db('speedtest', $connection);


function Random_Password($length) {
     srand(microtime(true)*1000000);
     $possible_charactors =  
"abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ";
     $string = "";
     while(strlen($string)<$length) {
         $string .= substr($possible_charactors, mt_rand(0, strlen 
($possible_charactors)),1);
     }
     return($string);
}



for ($i=0; $i<1000;$i++) {
	$str = "insert into test (pid, text) VALUES (".($i % 25).",'". 
(Random_Password(1024))."')";
	mysql_query($str, $connection);
}

mysql_close($connection);

?>


mysqli version
<?php

$mysqli = mysqli_connect('localhost', 'root', '', 'speedtest');

$stmt = $mysqli->prepare("SELECT * FROM test WHERE uid = ?");
for ($i=0; $i<100;$i++) {
	$stmt->bind_param("i", $rnd);
	$rnd = rand(0, 10) * 100;
	$stmt->execute();
	$stmt->bind_result($uid, $pid, $text);
	$stmt->fetch();	
}

mysqli_close($mysqli);

?>

mysql version
<?php

$connection = mysql_connect('localhost', 'root', '');
mysql_select_db('speedtest', $connection);



for ($i=0; $i<100;$i++) {
	$str = "SELECT * FROM test WHERE uid = " .rand(0, 10)*100;
	$res = mysql_query($str, $connection);	
	$row = mysql_fetch_assoc($res);
}

mysql_close($connection);

?>



The goal of using rnd(0, 10)*100 is to make sure the
records are cached using the mysql version and will mimic
a bit better what happens on the website, means only a part of the  
records
get's retrieved many times


As of the current state the mysql version is 5% faster
then the mysqli version with prepared statements using
the above code. This is properly because my version of mysql is 5.0.x
and prepared statements are not cached.

When I set my random string to rand(0, 50) * 20; the difference
is even bigger at 10% win for mysql (interesting, I don't know why yet)


If anybody is interested in my jmeter config then let me know
and I can send it over.


I hope that somebody can confirm and check my results (specially
the conform part :D )


I can publish the jMeter graphs somewhere upon request if you wish...

Ries



--
Ries van Twisk
Freelance TYPO3 Developer
email: ries at vantwisk.nl
web:   http://www.rvantwisk.nl/
skype: callto://r.vantwisk
Phone: + 1 810-476-4193