MySQL Forums
Forum List  »  Performance

Optimisation of a query using Group by and a Join
Posted by: Alex Brown
Date: October 19, 2015 12:20PM

Currently, there's no indexes

Orders Table:

ID
CustomerID

Users Table
ID
Email

The query is

UPDATE orders,
(SELECT id, c.email, t1.needed FROM customers c
JOIN (SELECT min(id) as needed, email FROM customers GROUP by email HAVING COUNT(*) > 1) t1 ON c.email = t1.email
WHERE c.email IN(SELECT email FROM customers GROUP by email HAVING COUNT(*) > 1)) t2
SET customerID = t2.needed
WHERE customerID = t2.id


There's about 300k rows in Customer table, and it's still running 1 hour later!

Is there anything I can do to improve the performance of the query?

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimisation of a query using Group by and a Join
1413
October 19, 2015 12:20PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.