CODEWITHSUNDEEP
phpmysqlpdo
user2213590
user2213590

Reputation:

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

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:

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.

Upvotes: 5

Views: 456

Answers (1)

Nick
Nick

Reputation: 10143

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

Upvotes: 1

Related Questions