Query running slow on script and producing error 500 when run in phpmyadmin

Question

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.

Answer 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);