MySQL: Delete all rows older than 30 days, but only if count more than one 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 &lt; UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))


I thought something like that

SELECT *
FROM parsed
WHERE TIMESTAMP &lt; UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 8 DAY))
    AND (
        (
            SELECT COUNT(*)
            FROM parsed
            GROUP BY item_id
            ) &gt; 1
        )


But this will not work. Thanks for help!
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 &lt; ...
        GROUP BY item_id
        HAVING COUNT(*) &gt; 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.