Created
July 18, 2012 22:02
-
-
Save srs81/3139201 to your computer and use it in GitHub Desktop.
MySQL insert, select performance testing in PHP
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
$dbhost = "localhost"; | |
$dbuser = "root"; | |
$dbpass = ""; | |
$dbname = "cake"; | |
$con = mysql_connect($dbhost, $dbuser, $dbpass); | |
if (!$con) die('Could not connect: ' . mysql_error()); | |
mysql_select_db($dbname, $con); | |
$result = mysql_query("CREATE TABLE `blogs` ( | |
`id` INTEGER NULL AUTO_INCREMENT DEFAULT NULL, | |
`name` VARCHAR(150) NULL DEFAULT NULL, | |
`content` MEDIUMTEXT NULL DEFAULT NULL, | |
`user_id` INTEGER NULL DEFAULT NULL, | |
`created` DATETIME NULL DEFAULT NULL, | |
PRIMARY KEY (`id`) | |
);"); | |
mysql_close($con); | |
runcmd("Insert"); | |
runcmd("SelectAll"); | |
runcmd("SelectRnd"); | |
function runcmd ($type) { | |
$num_entries = 40000; | |
$echo_num_inserts = 200; | |
$bulk_inserts = 1; | |
$dbhost = "localhost"; | |
$dbuser = "root"; | |
$dbpass = ""; | |
$dbname = "cake"; | |
$con = mysql_connect($dbhost, $dbuser, $dbpass); | |
if (!$con) die('Could not connect: ' . mysql_error()); | |
mysql_select_db($dbname, $con); | |
$starttime = microtime(true); | |
$inserts = ""; | |
foreach (range(1,$num_entries) as $i) { | |
// if (($i % $echo_num_inserts) == 0) echo "$i done.\n"; | |
if ($type === "Insert") { | |
$name = rand_string (20); | |
$content = rand_string (120); | |
$userid = rand(0,100); | |
$inserts .= "(0, '$name', '$content', '$userid', now())"; | |
$query = "INSERT INTO blogs (id, name, content, user_id, created) VALUES $inserts"; | |
} elseif ($type === "SelectAll") { | |
$query = "SELECT * FROM blogs LIMIT 1000"; | |
} else { | |
$id = rand(0,100); | |
$query = "SELECT * FROM blogs WHERE id=$id"; | |
} | |
if (($i % $bulk_inserts) == 0) { | |
mysql_query($query); | |
$inserts = ""; | |
} else { | |
$inserts .= ", "; | |
} | |
} | |
$endtime = microtime(true); | |
$qps = $num_entries / ($endtime - $starttime); | |
echo "$type qps = $qps\n"; | |
mysql_close($con); | |
} | |
function rand_string( $length ) { | |
$chars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"; | |
return substr(str_shuffle($chars),0,$length); | |
} | |
?> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment