I have a table containing around 4 million unique customers (with customerId each), but each customer can have multiple entries. There is no primary key. Each row has a creation dateTime saved as decimal number. customerId is indexed.
Now I want to find the solution to delete all but the latest row per customer that has the best performance. The thing is, this table will be constantly filled and the cleanup should be done once a day.
What I have tried:
- Use a cursor of "select customerId from customers" and iterate over it. Fetch the max creation time per customer. Delete all entries with the id and with a different creation time. -> Sadly, this is incredibly slow.
- "Smart" DELETE queries with subselects aren't possible, because MySQL does not allow calling select in a subquery.
- The client does not want to user triggers.
Any other ideas?