Clear old log entries from API while saving N of each type by user
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.)