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
