1

I need to cleanup my table of over 14.000.000 rows inside. I want to delete all entries, that are older than 30 days, but only if number of grouped items is larger than one. So, at least one item will stay.

Getting items and deleting them by date is not that problem. But it will delete all.

DELETE FROM parsed 
WHERE timestamp < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))

I thought something like that

SELECT *
FROM parsed
WHERE TIMESTAMP < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 8 DAY))
    AND (
        (
            SELECT COUNT(*)
            FROM parsed
            GROUP BY item_id
            ) > 1
        )

But this will not work. Thanks for help!

1

Any attempt to do this delete in a single query is likely to take hours, maybe days. If that is OK, then the code by @ypercube is probably good enough.

Otherwise, I would recommend tackling it in stages.

First find the items to delete. This will be relatively fast and non-invasive.

CREATE TEMPORARY TABLE t
    ( PRIMARY KEY(item_id) )
    SELECT item_id,
           MAX(timestamp) AS keeper
        FROM parsed
        WHERE timestamp < ...
        GROUP BY item_id
        HAVING COUNT(*) > 1;

We will use the PRIMARY KEY for efficiently walking through t. keeper is the row not to delete.

Now walk through t in chunks, deleting from parsed. But first, let's look at the DELETE:

DELETE parsed
  FROM parsed
  JOIN t
  WHERE parsed.item_id = t.item_id
    AND parsed.timestamp != t.keeper;

(Please verify that I got the multi-table syntax correct.)

Then put in a loop and add an AND. In pseudo code:

SELECT @a := MIN(item_id) from t;
loop:
    SELECT @z := item_id FROM t ORDER BY item_id LIMIT 100, 1;
    DELETE ...
      AND t.item_id BETWEEN @a and @z;
    SELECT @a := @z;
go back to loop

(There's still an endcase to deal with -- You need to exit the loop when when the last chunk has less than 100 items, and do the last few items.) More discussion.

| improve this answer | |
0

I think he best way is to copy those data you want to keep to another table, then delete them, then copy back in.

The main problem is that it is not simple to NOT delete ONE of the rows that are duplicate. By saving those in a separate temporary table - you avoid the issue of not having to delete them.

| improve this answer | |
0

I don't have MySQL on my PC so you may need to adjust a little, but here is how I would solve it using MS SQL Server.

-- Create Demo Table
DECLARE @MyTable TABLE ( IdField INT, DateField DATE, GroupField CHAR(1) )

INSERT INTO @MyTable ( [IdField], [DateField], [GroupField] ) 
    SELECT 1, '2015-03-01', 'A' UNION SELECT 2, '2015-04-01', 'A' 
    UNION SELECT 3, '2015-05-01', 'A' UNION SELECT 4, '2015-03-01', 'B' 
    UNION SELECT 5, '2015-04-01', 'C' UNION SELECT 6, '2015-05-01', 'C'

SELECT * FROM @MyTable

-- Get More than 30 Days Old & Count > 1
SELECT * FROM @MyTable WHERE [DateField] < '2015-04-13' 
    AND [GroupField] IN ( SELECT [GroupField] FROM @MyTable GROUP BY [GroupField] HAVING COUNT(*) > 1 )
| improve this answer | |
0

One way to write the DELETE statement:

DELETE FROM parsed AS p
WHERE p.timestamp < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))
  AND EXISTS
      ( SELECT *
        FROM parsed AS n
        WHERE p.item_id = n.item_id 
          AND p.timestamp < n.timepstamp
      ) ;
| improve this answer | |

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.