Clear old log entries from API while saving N of each type by user I have a log table that quickly gets over 20 GB and was hoping to develop a cron job that clears out the old entries while keeping the last 100 of each type for each user.

 id     |   user_id   |      api_route
1506          304           api/trayapp
1504          379           api/trayapp
1502          321           api/trayapp
1496          377           api/servers
1494          382           api/trayapp
1485          289           api/trayapp
1483          235           api/trayapp
1481          318           api/trayapp
1480          296           api/trayapp
1477          117           api/trayapp
1474          311           api/trayapp
1470          312           api/servers
1467          298           api/servers
1465          377           api/trayapp
1464          303           api/servers
1463          313           api/servers
1462          102           api/servers
1461          127           api/servers
1459          383           api/servers
1458          312           api/trayapp
1457          378           api/servers
1454          308           api/servers
1453          197           api/servers
1452          298           api/trayapp
1448          100           api/servers
1447          317           api/trayapp
1435          320           api/trayapp
1434          103           api/servers
1432          211           api/servers
1431          303           api/trayapp
1429          313           api/trayapp
1428          102           api/trayapp
1425          308           api/trayapp
1423          127           api/trayapp
1422          378           api/trayapp
1416          383           api/trayapp
1414          295           api/servers
1407          380           api/servers
1402          197           api/trayapp
1399          379           api/servers
1397          100           api/trayapp
1393          321           api/servers
1388          304           api/servers
1386          319           api/servers
1384          103           api/trayapp
1379          211           api/trayapp
1378          229           api/servers
1376          289           api/servers
1375          381           api/trayapp
1373          304           api/trayapp
1372          296           api/servers
1371          380           api/trayapp
1370          295           api/trayapp
1368          382           api/servers
1367          235           api/servers
1366          117           api/servers


I know I need to do a DELETE WHERE NOT IN (SELECT... but my first thought of how to do the select would be to use GROUP_CONCAT on the id, but then how would you use the results in the WHERE NOT IN? What would be your preferred method of removing all but the most recent? I do have created_at fields available but since the id is auto incremented it seems irrelevant.
Not going to be that simple.

Instead, write a script that walks through the table using the index on user_id for keeping track of where it is.

For each user, find the 100th from last, then DELETE any older ones.

This script will take minutes or hours, but who cares.  The script could even start over when it finishes -- keep going forever.

It will be desirable to have

INDEX(user_id, dt)


(I am assuming dt is the column giving the order within the user.)

More details...

The loop starts with finding the next $user_id:

SELECT user_id FROM tbl  WHERE user_id &gt; $last_user_id
    ORDER BY user_id  LIMIT 1;


To find the 100th newest:

SELECT dt FROM tbl  WHERE user_id = $user_id
    ORDER BY user_id DESC  LIMIT 100,1


If that comes back empty, there is nothing to delete for this $user_id.  Else, use the return value ($dt) and do:

DELETE FROM tbl  WHERE user_id = $user_id  AND dt &lt; $dt;


It might work to combine the SELECT and DELETE thus:

DELETE FROM tbl  WHERE user_id = $user_id
    ORDER BY user_id DESC  LIMIT 100, 99999


That says skip 100, then delete the next 99990 rows (working backward).

I have more discussion here .

(I have successfully implemented a similar script in a 200GB table.)