Why are certain types of prepared queries using PDO in PHP with MySQL slow?

Question

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; } 

Answer 1

Make sure you're telling PDO that the value is an integer not a string; if PDO puts it as a string, then MySQL will have to typecast the values for comparison. Depending on how it goes about this, it could cause major slowdowns by causing MySQL to avoid using an index.

I'm not completely sure about the behaviour here, but I have had this problem with Postgres a few years back...