5

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!

Community
  • 1
  • 1
Guybrush
  • 1,493
  • 2
  • 24
  • 50
  • do you store timestamp in any column which records time at which record was inserted. If you have done so you could use this timestamp value to delete past records. – srikanth r Jan 05 '17 at 04:04
  • I really wouldn't use MyISAM. Data integrity is far more important than sequential ids. – Strawberry Jan 05 '17 at 08:33
  • Sorry about delay, I posted this question 2 am in my time... @srikanthr, I have the id that is autoincrement, so I think that would be easier to rely on it instead of date/time fields... – Guybrush Jan 05 '17 at 12:00
  • @Strawberry, the engine is InnoDB, but as I said I just started with MySQL, so please give more information about your comment, I didn't understand... – Guybrush Jan 05 '17 at 12:00

2 Answers2

1

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

Pரதீப்
  • 85,687
  • 16
  • 112
  • 148
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;
Gurwinder Singh
  • 35,652
  • 5
  • 39
  • 62
  • GurV, your solution is not filtering by userid and I have no idea where to implement it... Please, can you update your answer? Thanks – Guybrush Jan 05 '17 at 12:38
  • Thank you! I will make tests... Please, what you think about this approach: "DELETE FROM access WHERE id NOT IN (SELECT id FROM (SELECT id FROM access WHERE userid = 10 ORDER BY id DESC LIMIT 100) foo) AND userid = 10;" I found here http://stackoverflow.com/questions/578867/sql-query-delete-all-records-from-the-table-except-latest-n ? – Guybrush Jan 05 '17 at 13:10
  • It's will do the same thing as my last query. And is hack, not a standard way. – Gurwinder Singh Jan 05 '17 at 13:11
  • Ok, this is what I wanted to know. Thanks! – Guybrush Jan 05 '17 at 13:27
  • Please, one last question... Why you used: "where b.id is null" ? – Guybrush Jan 05 '17 at 13:32
  • Since we're using a left join, those rows in the result of join will have b.id null which are not in the subquery part. It's to simulate the `NOT IN` clause. – Gurwinder Singh Jan 05 '17 at 13:34