Deleting older database entries

Question

I'm trying to keep the 10 most recent entries in my database and delete the older ones. I tried DELETE FROM people ORDER BY id DESC LIMIT $excess, but it just deleted the top 10 entries.

$query = "SELECT * FROM people";
$result = mysqli_query($conn, $query);
$count = mysqli_num_rows($result);
if ($count > 10) {
    $excess = $count - 10;
    $query = "DELETE FROM people WHERE id IN(SELECT id FROM people ORDER BY id DESC LIMIT '$excess')";
    mysqli_query($conn, $query);
}

http://stackoverflow.com/questions/578867/sql-query-delete-all-records-from-the-table-except-latest-n — user20232359723568423357842364, Jun 26 '13 at 17:32
A database runs fine even with a lot of `excess` data. If these entries are logs, you'll never have enough logs to slow down the database. — Dave Chen, Jun 26 '13 at 17:37

score 2 · Answer 1 · answered Jun 26 '13 at 17:35

2

Something like this? Gets the ten latest ids in the subquery, then deletes all of the other ids.

DELETE FROM people WHERE id NOT IN (SELECT id FROM PEOPLE ORDER BY id DESC LIMIT 10)

answered Jun 26 '13 at 17:35

Joel Hinz

24,719
6
62
75

I'm getting #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' ver. 5.0.91 – Rice_Crisp Jun 26 '13 at 17:41
Oh, you're using an older version of MySQL, then. If you can't update, I'd suggest making two queries - one to find the tenth id, and then using that to delete all queries that have older ids. – Joel Hinz Jun 26 '13 at 17:43

score 2 · Accepted Answer · answered Jun 26 '13 at 17:39

2

You can use this:-

DELETE FROM `people`
WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT id
    FROM `people`
    ORDER BY id DESC
    LIMIT 10
  ) 
);

Also your query is logically incorrect and you are fetching the records in descending order. i.e. Latest to older and you are deleting the most recent records. Use ASC instead.

answered Jun 26 '13 at 17:39

Vivek Sadh

4,230
3
32
49

I had to add an alias after the first ")" as suggested by the first comment's link, but it seems to work. Thanks. – Rice_Crisp Jun 26 '13 at 17:59

score 0 · Answer 3 · answered Jun 26 '13 at 17:40

Your logic is all over the place, [you should ORDER BY ASC, not DESC] and your query will take ages if there are [for example] 10,000 entries because you'll have an IN clause with 9,990 entries to compare all 10,000 to.

Select the 10 most recent, and delete where NOT in.

DELETE FROM people
  WHERE id NOT IN(
    SELECT id 
    FROM people
    ORDER BY id DESC
    LIMIT 10
  )

score -1 · Answer 4 · answered Jun 26 '13 at 17:33

-1

Maybe find the ID of the 10th element and then delete all rows which are older?

answered Jun 26 '13 at 17:33

user2520968

358
1
3
11

Deleting older database entries

4 Answers4