2

I have a query that seems to take a good 30 seconds to execute even when the query produces no results. There are 4000 lines within the database and queries that produce all results run within fractions of a second. Could a greater mind please tell me where i am going wrong as wherever i have asked there is no error in it.

SELECT ShipAddress1, CustomLabel, Quantity, PhService, UniqueID, FullName, ShipPostCode
FROM internetorders WHERE ShipAddress1 IN (SELECT ShipAddress1 FROM internetorders
WHERE Complete = '0' AND LabelCreated = '0' GROUP BY ShipAddress1 HAVING Count(*) > 1)
AND Complete = '0' AND LabelCreated = '0' ORDER BY ShipAddress1

When running in my script the query returns the result but slowly, when running in phpmyadmin i get error 500.

Also this query is obviously returning all results with a duplicate record in ship address one. How could i have it return all results that are same in ship address 1 and post code only?

Thanks in advance.

1

You should refactor the query as follows

SELECT
    B.ShipAddress1, B.CustomLabel,
    B.Quantity, B.PhService, B.UniqueID,
    B.FullName, B.ShipPostCode FROM
FROM
(
    SELECT ShipAddress1 FROM internetorders
    WHERE Complete = '0' AND LabelCreated = '0'
    GROUP BY ShipAddress1 HAVING Count(*) > 1
) A LEFT JOIN internetorders B USING (ShipAddress1);

If ShipAddress1 is not indexed, then create an index for it

ALTER TABLE internetorders ADD INDEX (ShipAddress1);
|improve this answer|||||
  • Thank you very much! This worked a treat. Your a life saver! – user3314085 Jan 22 '15 at 20:05
  • If my answer helped, please click on the checkmark in the upper left corner of my answer to mark it accepted. – RolandoMySQLDBA Jan 22 '15 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.