Geek Answers Handbook

PDO - a specific request is much slower than manual execution

I experience a sharp slow execution of a request with a specific request (see below).

It is strange that when you start the request manually (using phpMyAdmin) it takes only a second to complete , and when you start it from PHP with PDO, it takes almost 10 minutes!

Other requests are executed normally (equal durations between PDO and phpMyAdmin).

Technical details:

  • I use Google AppEngine and Cloud SQL.
  • Running PHP5.5
  • The application instance executing the PHP code is not busy.
  • The query result contains about 10,000 lines

Request in progress:

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 of this problem? It cannot be associated with indexes, since the query runs fine under phpMyAdmin.

+4
1

. - :

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
+1

More articles:


All Articles