4

I'm running into a super slow PDOStatement::fetchAll() that is just driving me nuts. My query is running in less than 0.1 seconds. Also in the MySQL terminal, I get my output on my screen in less than 0.1 seconds. But running fetchAll() on the PDOStatement takes 2.5 seconds.

// $_DB is my PDO class instance.
$tStart = microtime(true);
$q = $_DB->prepare('SELECT id FROM ... WHERE ... LIMIT 1000');
$q->execute($aArgs);
var_dump(round(microtime(true) - $tStart, 5));
$aIDsFiltered = $q->fetchAll(PDO::FETCH_COLUMN, 0);
var_dump(round(microtime(true) - $tStart, 5));exit;

This outputs:

float(0.0612)
float(2.58708)

OK, seriously? How can I get my results in the MySQL console in less than 0.1 seconds, but PHP takes 2.5 seconds to fetch those 1000 results and put it in a simple array? Come on, a simple for loop putting 1000 numbers in an array one by one takes 0.001 seconds...!!! What am I missing here? What can I use as an alternative? I've googled and searched and I can't find a solution :( Thanks in advance!

EDIT: Not only is the duration of the fetchAll() related to the number of returned results... but also related to the size of $aArgs. Sending no arguments makes fetchAll() return in 0.01 seconds, even with with 50K results! Feeding the execute() call 47K arguments makes fetchAll() then take 120 seconds to run. But it's not the creation of the large query string that causes this (doesn't execute() do that by the way?), because the same 47K arguments, but a LIMIT to 1K results takes only 2.5 seconds... As suggested, I've verified that the EXPLAIN output is the same for PDO versus using the MySQL console. I also don't see MySQL working hard, it's the Apache process (thus PHP) that's eating CPU for all this time. ALSO: using the older mysql_* interface, the fetching of the results takes 0.03 seconds.

Background info

For the curious:

  • It's a local MySQL 5.5 database, so not a remote server.
  • PHP version: 5.4.4.
  • The use case here, is that there is a number of filters in the system (one filter = one database query), run in a user's favorite order, where each filter gets fed the IDs of the matching entries from the previous filter run, and it should return the IDs of the remaining matching entries (hope this is clear). In this way, the filters are used to, in the end, select a small subset of database entries that match all filters.
  • Some times I actually have 47K results to return, and then the delay is 2.1 minutes while the query takes less than 1 second, not acceptable in my case.
  • I understand that I could drastically lower the number of results by combining several filters into one database query. However, the filters are selected by users (so unlimited combinations), they can have joins and checks on different database tables, and last but not least, they want to see statistics on how many results each filter returns.
ifokkema
  • 49
  • 4
  • 2
    PHP will have to transfer the data from the database's storage into its own memory and post process it there. Depending on how the connection between PHP and the database is, this may incur some delay. It's impossible to say whether 2.5 seconds is reasonable in your situation or not, since we don't know details of the setup. – deceze Apr 02 '14 at 12:50
  • deceze is right, it remains to say that with a proper setup the time PHP is using will be comparable to that of the `mysql` console client. But keep in mind that the console client will not do a fetchAll.. It will iterate over the result set one by one. This saves a lot of memory. You should do the same with PHP otherwise the results aren't comparable – hek2mgl Apr 02 '14 at 12:54
  • Thanks for this! Any suggestions on "a proper setup"? It's simply a local MySQL (5.5.31) database... Removing the special PDO flags I set on init has no effect, so I'm not doing anything weird there. Oh, and looping with a fetch() does not speed things up... – ifokkema Apr 02 '14 at 13:00
  • Look at [this question](http://stackoverflow.com/questions/12336457/php-takes-90x-longer-to-run-query-than-mysql-client), the comments and answers on it. Do you get the same results if you use the poor old `mysql_query` interface? What is your PHP version and your MySQL extension version? – DCoder Apr 02 '14 at 13:18
  • @DCoder: thanks! Guess what... the old mysql_* interface runs the fetch in 0.03 seconds! So definitely related to PDO... But I noticed something else really weird, and I will update my question in a bit, when I have all the results from my tests. – ifokkema Apr 02 '14 at 14:06
  • what is the size of $aArgs in your initial example? – Your Common Sense Apr 02 '14 at 14:30
  • @YourCommonSense: a little over 47.000 entries. With that size, the fetchAll() takes between 0.05s (added LIMIT 1 to query) and 120s (no limit == 47K results). I know that really looks like it's the query itself, but it's Apache taking up all the CPU for 2 minutes... – ifokkema Apr 02 '14 at 14:40
  • Although it's apparently a PDO-related issue, but it's apparently caused by the way **you** decided to torture PDO with 50k paramaters. – Your Common Sense Apr 02 '14 at 14:49
  • Reading the background information in my post, do you have a better suggestion of structuring these filters? – ifokkema Apr 02 '14 at 15:54
  • You have them already: "I could drastically lower the number of results by combining several filters into one database query." Dunno for the filters but these 50k apparently coming from the same database and have to be used by means of some SQL, being kept on the DB side – Your Common Sense Apr 02 '14 at 17:02
  • 1
    Then the question would be how to fix everything following the "however" directly following the piece of text you quote... -.- Question remains why PDO is perfectly capable of handling 50K arguments in little time, perfectly capable of fetching 50K results in fetchAll() in little time, but doesn't handle a combination of both at all, while native mysql client and PHP's mysql_* functions have no issues at all... – ifokkema Apr 02 '14 at 17:36
  • 1
    Related: https://stackoverflow.com/questions/4350718/why-are-certain-types-of-prepared-queries-using-pdo-in-php-with-mysql-slow - https://stackoverflow.com/questions/50375815/pdo-statement-taking-400x-longer-than-mysql-cli – Paul Spiegel May 17 '18 at 09:26

1 Answers1

0

Your query's SQL could tell us a bit. Try running an EXPLAIN SELECT ... and seeing what the query plan is. Check that indexes are being used properly, etc.

However the difference between a simple execute call and a fetchAll call is probably down to a few factors:

  • the performance of the underlying driver - are you using the mysqlnd driver, or a legacy version?
  • the size of the result set - loading all the data in to array structures will take memory and time
    • it's worth noting that PDOStatement->execute only returns a boolean value anyway

In the first instance I'd make sure you've got enough memory and CPU allocated to PHP, and check the mysql driver that PHP is using. It should be mysqlnd.

Will Morgan
  • 4,470
  • 5
  • 29
  • 42
  • 1
    Thanks for taking the time to answer! The query however, is not the problem. It is finished in less than 0.1 seconds (not just in PHP, also in the console). I switched to the mysqlnd driver, and there was no difference. The size is, as displayed in the query, 1000 results (just numeric IDs). In real life, 47K results can be returned (and then the problem gets bigger of course). – ifokkema Apr 02 '14 at 13:26
  • 1
    Why post your idle musings in the form of answer? A comment under the question perfectly fits for them. – Your Common Sense Apr 02 '14 at 13:29