0

I have a table for chat messages like:

id | fk_user_1_id | fk_user_2_id | message | timestamp

I only need to keep the 50 most recent messages around per friend pair. What's the simplest way to do this? An awful way is:

  1. Insert a new message.
  2. Select the 50 most recent messages, sorted in reverse chronological order.
  3. If the result has more than 50 records, grab the timestamp of the 50th one.
  4. Delete all records older than that timestamp for the friend pair.

Is there a simpler way to do this? I'm using mysql.

Thanks

user3203425
  • 2,919
  • 4
  • 29
  • 48
  • Maybe you could set up a `TRIGGER` on `INSERT` to delete the 51st record if one is present. – tadman May 08 '14 at 17:31
  • @tadman not delete the 51st record, i think he wants to delete -50th record. –  May 08 '14 at 17:33
  • I'm guessing this is not terribly important in this case, but your algorithm is susceptible to race conditions. If two transactions try to execute it concurrently, you may end-up with more than 50 rows. BTW, @BrettFromLA's answer also has (slightly different) race condition. – Branko Dimitrijevic May 09 '14 at 10:54

1 Answers1

0

A slightly-less terrible way:

Add a counter to each message.
When there's a new message:
    Assign its counter a value of MAX(counters)+1.
    Also delete any message with a counter value less than (current_counter)-50.
BrettFromLA
  • 906
  • 1
  • 7
  • 17