Autopruning after a specified amount of row are created?

Question

Basic question, sorry. Basically, I have a script that creates a MySQL entry each time someone visits the page. A logging script. However, I want to make it autoprune after, say, 100 visits.

For example in pseudo code:

if amount of rows > 100 { delete rows 1+ until amount of rows == 100 }

So in a nutshell, each time a new row is added after 100, it needs to automatically remove the row with the smallest id (My primary key).

Answer 1

You most likely have to lock the table in order to get the count and then delete the "right" amount of old records. Therefore I'd rather limit the SELECT to 100 rows and keep more records in the table even if they are superfluous. You can still delete old records, e.g. with a cron job once every x hours or with a random probability of x per-mile:

if ( 10 > rand(0, 8000) ) {
  LOCK TABLES foo
  SELECT COUNT(*) as cnt FROM foo
  if ( $cnt > 100 ) {
    $cnt -= 100;
    DELETE FROM foo ORDER BY id DESC LIMIT $cnt
  }
  UNLOCK TABLES
}

or something like that...

(as far as I know the ORDER BY and LIMIT clause for DELETE statements are MySQL specific. Better keep that in mind.)

Answer 2

Another possible option would be to use database TRIGGERS (http://forge.mysql.com/wiki/Triggers)

It may be overkill in this scenario but would check the table every time a row is inserted. An example (which I haven't tested!) :

Create Trigger prune_MyTable AFTER INSERT ON MyTable FOR EACH ROW BEGIN DECLARE num_rows INTEGER; DECLARE lowest_id INTEGER;

SELECT COUNT(*) INTO num_rows FROM MyTable;

IF num_rows > 100 THEN SELECT MIN(id) INTO lowest_id FROM MyTable LIMIT 100;

UPDATE MyTable DELETE WHERE id < lowest_id;

END IF; END$$