Skip to content

Instantly share code, notes, and snippets.

@srs81
Created July 18, 2012 22:02
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save srs81/3139201 to your computer and use it in GitHub Desktop.
Save srs81/3139201 to your computer and use it in GitHub Desktop.
MySQL insert, select performance testing in PHP
<?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