PDO - a specific query is drastically slow compared to manual execution

Question

I'm experiencing a drasticily slow query execution with a specific query (written below).

The weird thing is, when running the query manually (using phpMyAdmin), it only takes a second to complete, while running it from PHP with PDO, it takes almost 10 minutes!

Other queries execute normally (equal durations between PDO & phpMyAdmin).

Technical details:

I'm using Google's AppEngine & Cloud SQL.
Running PHP5.5
The app engine instance executing the php code isn't busy.
The query results with about 10,000 rows

The query I'm running:

SELECT s.saleID,s.year,s.kilometrage,s.hand,s.price,s.pictures,u.platform,s.date,
   (SELECT AVG(price) FROM sales s2 WHERE s2.carID = s.carID AND s2.year = s.year HAVING COUNT(s2.saleID)>5) AS avgPrice
   FROM sales s JOIN users u ON u.ID = s.sellerID LEFT JOIN sold so ON s.saleID = so.saleID WHERE so.saleID IS NULL

Any hints on this issue? It can't be related to indexes since the query runs perfectly well under phpMyAdmin.

Edit

In case someone is also having this - seems like PMA adds an implicit LIMIT on the query for paging, which makes everything runs faster in some cases.

I am facing the same issue with one query. My only guess is that it's related to the joins I do. If I remove one of them it works fine even via Pdo — Lelio Faieta, Mar 05 '16 at 16:26
But why would the JOINs slow the query down only when executed via PDO? I mean, if the query is slow, it should be slow on both PDO and phpMyAdmin. — , Mar 05 '16 at 16:30
That's the point I haven't understood yet. I asked the same in a unanswered question here on so — Lelio Faieta, Mar 05 '16 at 16:31
[This answer](http://stackoverflow.com/a/13345776/3585500) might help. There's a PDO EXPLAIN command to see what the planner is doing. There's also index hinting to tell it how to run. See also [the answer below that about type casting](http://stackoverflow.com/a/21338800/3585500). — ourmandave, Mar 05 '16 at 16:40

score 1 · Answer 1 · answered Mar 06 '16 at 15:16

Try to write query without subquery in field list. Something like this:

SELECT s.saleID, s.year, s.kilometrage, s.hand, 
   s.price, s.pictures, u.platform, s.date,
   t.avgPrice
FROM sales s 
JOIN users u ON u.ID = s.sellerID 
LEFT JOIN sold so ON s.saleID = so.saleID 
LEFT JOIN (
    SELECT AVG(s2.price) as avgPrice, s2.carID, s2.year
    FROM sales s2 
    GROUP BY s2.carID, s2.year
    HAVING COUNT(s2.saleID) > 5
) t ON t.carID = s.carID AND t.year = s2.year
WHERE so.saleID IS NULL

PDO - a specific query is drastically slow compared to manual execution

Edit

1 Answers1