Bug #53458 [Com]: Non-linear performance degradation on certain prepared SELECT queries

Edit report at http://bugs.php.net/bug.php?id=53458&edit=1 ID: 53458 Comment by: don at smugmug dot com Reported by: don at smugmug dot com Summary: Non-linear performance degradation on certain prepared SELECT queries Status: Open Type: Bug Package: PDO related Operating System: CentOS 5.5 PHP Version: 5.3.3 Block user comment: N Private report: N New Comment: FYI, setting PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to true/false didn't have a large impact. Still ~10X slower than any of the other methods either way. (Buffered was slightly faster) Previous Comments: ------------------------------------------------------------------------ [2010-12-02 23:39:10] don at smugmug dot com Here's my PHP build configuration: './configure' '--enable-exif' '--with-gd' '--enable-gd-native-ttf' '--with-jpeg- dir=/usr' '--with-png-dir=/usr' '--with-freetype-dir=/usr' '--with-zlib' '-- enable-inline-optimization' '--with-bz2' '--with-apxs2' '--with-xmlrpc' '--with- curl' '--with-libdir=lib64' '--with-pdo-mysql=mysqlnd' '--with-mcrypt' '--enable- bcmath' '--with-gmp' '--enable-mbstring' '--with-mysql=mysqlnd' '--with-openssl' '--with-mysqli=mysqlnd' ------------------------------------------------------------------------ [2010-12-02 23:29:50] don at smugmug dot com Description: ------------ When retrieving results from prepared PDO statements against MySQL, we get performance that diverges from expected by 10X or more on results as low as 10000 rows. This only occurs for 'SELECT ... WHERE Id IN ( .. )' queries. The attached script provides two PDO prepared approaches ('row-prepared', default, and 'all-prepared') as well as a variety of control methods that use non-prepared PDO queries, straight MySQL, and prepared queries using MySQLi. Only PDO with prepared queries exhibits the problem. If the query is broken up into chunks that return 1000 rows or less prior to execution, then combined afterwards in PHP, performance is as expected. Test script: --------------- You can get the sample script from: http://www.smugmug.com/test/pdo-problem.php.gz Expected result: ---------------- pdo-problem.php?type=row-prepared and pdo-problem.php?type=all-prepared should return in ~0.5s, like the other types (row, all, chunk, mysql, mysqli). Actual result: -------------- pdo-problem.php?type=row-prepared and pdo-problem.php?type=all-prepared return in ~6s instead of ~0.5s ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/bug.php?id=53458&edit=1