MySQL - Efficiently delete all records except last N

Question

I started learning SQL recently and now was asked to perform a simple deletion on a table, keeping the last 100 records for each user. I was researching the best approach (more efficient way) to archive this, and found some possible solutions (SQL query: Delete all records from the table except latest N? , Delete all but top n from database table in SQL), but it is kind intricate for me to choose one based on efficiency. So I am here to ask for your help.

Here is the table called "access" where we keep users access log.

access:
- id (autoincrement) - primary
- userid (integer 11) - key
- refer (varchar 100)
- date (date/time)

My idea is to delete old records from the userid everytime this same user enter the system, just before inserting the new log.

I have tried this code bellow but got error: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

DELETE FROM
  access
WHERE
  id NOT IN (
    SELECT id FROM access WHERE userid = 10 ORDER BY id DESC LIMIT 100
  );

Please, can you suggest me some solution? Thanks!

Answer 1

Try DELETE JOIN:

delete a from access a left join (
    select id
    from access
    order by id desc limit 1000
) b on a.id = b.id
where b.id is null;

If you want to keep top 1000 records of a given user (say 123) from deleting :

delete a from access a left join (
    select id
    from access
    where userid = 123
    order by id desc limit 1000
) b on a.id = b.id
where b.id is null;

If you want to delete rows only for user 123 except the top 1000 for that user:

delete a from access a left join (
    select id
    from access
    where userid = 123
    order by id desc limit 1000
) b on a.id = b.id
where b.id is null
and a.userid = 123;

Answer 2

Am not a expert in Mysql not sure why it is not allowed in Mysql. Try something like this

DELETE a
FROM   access a
       INNER JOIN (SELECT id
                   FROM   access
                   WHERE  userid = 10
                   ORDER  BY id DESC 
                   LIMIT 100) b
               ON a.id <> b.id 

Note : This might not be efficient as mentioned in comments