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-bug-53458-pdo-problem.php

<?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";

Hello! I meet the promble like you say now and I want to know is this PDO bug have fix?