Edit Question - KnowledgeHub

Title Body

When using SELECT * FROM table WHERE Id IN ( .. ) queries with more than 10000 keys using PDO with prepare()/execute(), the performance degrades ~10X more than doing the same query using mysqli with prepared statements or PDO without using prepared statements. More strange details: More typical SELECT statements that don't have the WHERE Id IN( ..) clause perform fine even with 100K+ rows. SELECT * FROM table WHERE Id for example is fast. The performance degradation occurs after prepare()/execute() is complete - it's entirely in PDOStatement::fetch() or PDOStatement::fetchAll() . The MySQL query execution time is tiny in all cases - this isn't a case of a MySQL optimization. Splitting the 10K query into 10 queries with 1K keys is performant. Using mysql, mysqli with prepared statements, or PDO without prepared statements is performant. PDO w/prepared takes ~6 seconds on the example below, while the others take ~0.5s. It gets worse in a non-linear fashion the more keys you have. Try 100K keys. Sample code: // $imageIds is an array with 10K keys $keyCount = count($imageIds); $keys = implode(', ', array_fill(0, $keyCount, '?')); $query = "SELECT * FROM images WHERE ImageID IN ({$keys})"; $stmt = $dbh->prepare($query); $stmt->execute($imageIds); // until now, it's been fast. fetch() is the slow part while ($row = $stmt->fetch()) { $rows[] = $row; }

Tags (comma-separated) Cancel