Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PHP Bug #53458 reproduce script. When fetching 10K rows using prepared statements, PDO takes ~6 seconds. When fetching the same 10K rows in 1K chunks, PDO takes ~0.5 seconds. Non-prepared fetches take ~0.25 seconds. There's some sort of non-linear falloff in performance. See: https://bugs.php.net/bug.php?id=53458
<?php
/**
* For some reason, SELECT ... WHERE Id IN ( .. ) queries using prepared
* statements in PDO (either emulated or not) cause severe performance
* degradation over 1000 rows.
*
* Normal SELECT queries, including SELECT * FROM table, with hundreds of
* thousands of rows don't exhibit this problem, only WHERE IN queries.
*
*
* How to reproduce:
*
* Create a MySQL table called 'images' with a Primary Key called ImageID, with
* any number of columns (including just the PK)
*
* Fill it up with at least 10K rows, more if you want to see this thing
* seriously fall off a cliff.
*
* Configured the db settings and execute via a web browser (trivial to make it
* run cli too, just add arg support)
*
*/
$dbHost = "";
$dbUser = "";
$dbPassword = "";
$dbName = "";
$dsn = "mysql:host={$dbHost};dbname={$dbName}";
// default is 10K, problem easily shows up here and gets worse with more
$limit = 10000;
if (isset($_GET['limit'])) {
$limit = $_GET['limit'];
}
// default is PDOStatement::fetch() on prepared statement
$getType = "row-prepared";
if (isset($_GET['type'])) {
$getType = $_GET['type'];
}
// default is emulated prepares
$attr = array(PDO::ATTR_EMULATE_PREPARES => true);
$prepares = "emulated";
if($_GET['prepared'] == "real") {
$attr = array(PDO::ATTR_EMULATE_PREPARES => false);
$prepares = "real";
}
// grab a handle and execute the query to get a pool of ids
$dbh = new PDO($dsn, $dbUser, $dbPassword, $attr);
$query = "SELECT ImageID FROM images LIMIT $limit";
$stmt = $dbh->prepare($query);
$stmt->execute();
// stick the ids in a useful array
$rows = $stmt->fetchAll($result);
foreach ($rows as $row) {
$imageIds[] = $row['ImageID'];
}
unset($rows);
echo "doing " . count($imageIds) . " with $getType with $prepares prepared
statements<br/>\n";
$queryStart = microtime(true);
/** various different methods, 'row-prepared' and 'all-prepared' are the
* problem methods, the others are provided as controls.
*
* The PDO prepared methods return in ~6 seconds, all the others return in
* ~.5s or less.
*
*/
switch ($getType) {
// while loop with PDOStatement::fetch() on prepared query (SLOW!)
case "row-prepared":
$keyCount = count($imageIds);
$keys = implode(', ', array_fill(0, $keyCount, '?'));
$query = "SELECT * FROM images WHERE ImageID IN ({$keys})";
$stmt = $dbh->prepare($query);
$stmt->execute($imageIds);
// here's where the trouble starts
$whileStart = microtime(true);
while ($row = $stmt->fetch()) {
$rows[] = $row;
}
$whileStop = microtime(true);
$whileElapsed = $whileStop - $whileStart;
break;
// use PDOStatement::fetchAll() with a prepared query (SLOW!)
case "all-prepared":
$keyCount = count($imageIds);
$keys = implode(', ', array_fill(0, $keyCount, '?'));
$query = "SELECT * FROM images WHERE ImageID IN ({$keys})";
$stmt = $dbh->prepare($query);
$stmt->execute($imageIds);
// here's where the trouble starts
$whileStart = microtime(true);
$rows = $stmt->fetchAll();
$whileStop = microtime(true);
$whileElapsed = $whileStop - $whileStart;
break;
// while loop with PDOStatement::fetch() on non-prepared query (FAST!)
case "row":
$sqlIds = implode(", ", $imageIds);
$query = "SELECT * FROM images WHERE ImageID IN ({$sqlIds})";
$stmt = $dbh->query($query);
$whileStart = microtime(true);
while ($row = $stmt->fetch()) {
$rows[] = $row;
}
$whileStop = microtime(true);
$whileElapsed = $whileStop - $whileStart;
break;
// use PDOStatement::fetchAll() with a non-prepared query (FAST!)
case "all":
$keyCount = count($imageIds);
$keys = implode(', ', $imageIds);
$query = "SELECT * FROM images WHERE ImageID IN ({$keys})";
$stmt = $dbh->query($query);
$whileStart = microtime(true);
$rows = $stmt->fetchAll();
$whileStop = microtime(true);
$whileElapsed = $whileStop - $whileStart;
break;
// break the ids up into chunks of 1000 and query + PDOStatement::fetch()
// on each chunk. Unbelievably, this is faster than non-chunked.
case "chunk":
$idsChunks = array_chunk($imageIds, 1000, true);
foreach ($idsChunks as $idsArray) {
$idsArray = array_values($idsArray);
$keyCount = count($idsArray);
$keys = implode(', ', array_fill(0, $keyCount, '?'));
$query = "SELECT * FROM images WHERE ImageID IN ({$keys})";
$stmt = $dbh->prepare($query);
$stmt->execute($idsArray);
$whileStart = microtime(true);
while ($row = $stmt->fetch()) {
$rows[] = $row;
}
$whileStop = microtime(true);
$whileElapsed += $whileStop - $whileStart;
}
break;
// straight MySQL for reference (FAST!)
case "mysql":
mysql_connect($dbHost, $dbUser, $dbPassword);
mysql_select_db($dbName);
$sqlIds = implode(", ", $imageIds);
$query = "SELECT * FROM images WHERE ImageID IN ($sqlIds)";
$result = mysql_query($query);
$whileStart = microtime(true);
while($row = mysql_fetch_assoc($result)) {
$rows[] = $row;
}
$whileStop = microtime(true);
$whileElapsed = $whileStop - $whileStart;
break;
// MySQLi prepared statement for reference (FAST!)
case "mysqli-prepared":
$keyCount = count($imageIds);
$keys = implode(', ', array_fill(0, $keyCount, '?'));
$query = "SELECT * FROM images WHERE ImageID IN ({$keys})";
$mysqli = new mysqli($dbHost, $dbUser, $dbPassword, $dbName);
$stmt = $mysqli->prepare($query);
$types = str_repeat("i", count($imageIds));
$refs = array();
$refs[] = $types;
foreach ($imageIds as $key=>$value) {
$refs[] = &$imageIds[$key];
}
call_user_func_array(array($stmt, 'bind_param'), $refs);
$stmt->execute();
$meta = $stmt->result_metadata();
while ($field = $meta->fetch_field()) {
$params[] = &$row[$field->name];
$paramRefs[] = &$params[$key];
}
call_user_func_array(array($stmt, 'bind_result'), $paramRefs);
$whileStart = microtime(true);
while ($stmt->fetch()) {
$a = array();
foreach ($row as $key => $value) {
$a[$key] = $value;
}
$rows[] = $a;
}
$whileStop = microtime(true);
$whileElapsed = $whileStop - $whileStart;
break;
}
$queryStop = microtime(true);
$rowCount = count($rows);
$whileElapsed = round($whileElapsed, 3);
$queryElapsed = round($queryStop - $queryStart, 3);
echo "got $rowCount rows in $queryElapsed seconds ... while() took
$whileElapsed seconds <br/><br/>\n\n";
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.