mysql delete all rows and keep latest x left [part 2]

Question

*First, thanks for the help in the first part of that question:

Situation again:

"I have in my table "mytable" fields, entryid (autoincrement) and roomid.. and I would like to delete all roomid = 1, except the last 3"

 entryid,  roomid
   1           1      
   2          55
   3           1
   4          12
   5           1
   6          44
   7           1
   8           3
   9           1

now i solved it with that:

// Delete older comments from room 1 (keep last 3 left)
// Step 1:
$sql_com = "SELECT id FROM `mytable` WHERE roomid = '1'";
$result = mysql_query ($sql_com); $num_rows = mysql_num_rows($result);   

// Step 2:
if ($num_rows > 3) {
  $sql_com = "SELECT id FROM `mytable` WHERE roomid = '1' ORDER BY id DESC LIMIT 3,1"; 
  $result = mysql_query ($sql_com);
  $row = mysql_fetch_array($result, MYSQL_NUM);
}

// Step 3: 
$sql_com = "DELETE FROM `mytable` WHERE roomid = '1' AND id < ".$row[0];
$result = mysql_query ($sql_com);

This works fine for now.

1. Question: I needed to make Step 1 because without i would get an mysql error in step 2 at LIMIT 3,1 if there are less than 3 entries for roomid =1 in my table. (Limit 3,1 expect that there are minimum 3 entries). How could I solve that so I could remove step 1.

2. MainQuestion: How to bring all this 3 Steps into ONE STEP :) Is that possible and how?

Thx Chris

p.s. just for info: this routine may be started 500 Times a Second with a Database of 3Million Entries. So it need to be resource friendly as possible.

Answer 1

DELETE FROM `mytable` WHERE roomid = '1' AND roomid <= (SELECT * FROM (SELECT roomid FROM `mytable` WHERE roomid = '1' ORDER BY roomid DESC LIMIT 3,1) tmp);

Please notice if you remove "select * from (...) tmp)" you may get an error.

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Also I think your code delete all rows except the last 4 ones (not 3).

Answer 2

DELETE mytable FROM mytable
JOIN (
  SELECT id FROM mytable
  WHERE roomid = 1
  ORDER BY id DESC
  LIMIT 3,18446744073709551615
) AS t2
ON mytable.id = t2.id;