Mysql – How to delete all but the latest entry per customer in the fastest way

deleteMySQLperformancequeryquery-performance

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?

Best Answer

Draft code:

CREATE PROCEDURE clear_table() AS
BEGIN
    CREATE TABLE temptable LIKE datatable;
    INSERT INTO temptable (customerID, creationDatetime)
        SELECT customerID, MAX(creationDatetime)
        FROM datatable
        GROUP BY customerId;
/* if there exists another fields in tables, then: */
/*  UPDATE temptable, datatable
        SET temptable.field1 = datatable.field1, 
            ... , 
            temptable.fieldN = datatable.fieldN
        WHERE temptable.customerID = datatable.customerID
          AND temptable.creationDatetime = datatable.creationDatetime;
*/
    DROP TABLE datatable;
    ALTER TABLE temptable RENAME TO datatable;
END;

To increase performance - create index by (customerID, creationDatetime).

APPROACH #1 : DELETE JOIN

If @DeleteCount < 5% of @CompleteCount, use a list of IDs to DELETE from mytable:

CREATE TABLE mytable_delete_id
SELECT id FROM mytable WHERE LOCATE('.ac.',email) > 0;
ALTER TABLE mytable_delete_id ADD PRIMARY KEY (id);
DELETE B.* FROM mytable_delete_id A INNER JOIN mytable B USING (id);
DROP TABLE mytable_delete_id;

This helps take the guess work out of which rows to DELETE by using a SELECT to get the IDs. The JOIN takes care of the DELETE.

APPROACH #2 : CREATE A NEW TABLE

If you have the following conditions:

@DeleteCount > 30% of @CompleteCount
your table does not have foreign keys/constraints

you could just create a new table and filter out the rows with .ac. in the email column:

CREATE TABLE mytable_new LIKE mytable;
#insert rows which does not contain '.ac.' from mytable
INSERT INTO mytable_new SELECT * FROM mytable WHERE LOCATE('.ac.',email) = 0;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;
DROP TABLE mytable_old;

EPILOGUE

You can do either of these methods without checking table counta ahead of time. They will both work. If you are concerned that .ac. is in a huge number of rows, go with APPROACH #2. If there are not that many occurrences, go with APPROACH #1.

MySQL query taking too long

To do the filtering sooner, change the subquery to

( SELECT email,
         COUNT(*) AS email_ct
      FROM oc_order
      GROUP BY  email
      HAVING COUNT(*) > 3 ) AS xxx

Then remove xxx.`Orders Count` >3 as being redundant.

To make the subquery run faster, oc_orders needs INDEX(email)

In oc_order_total, change KEY order_id (order_id) to KEY order_id (order_id, code)

With email_ct, you can add this to the main part of the query:

email_ct as 'Orders Count',

Best Answer

Related Solutions

Mysql – Fastest way to delete matching rows in MySQL

APPROACH #1 : DELETE JOIN

APPROACH #2 : CREATE A NEW TABLE

EPILOGUE

MySQL query taking too long

Related Question