Search code examples
mysqldatabase-management

Increment a value for entire database

So we have a database. Every so often we'll add a new data set and the oldest dataset should be removed.

Say the DB is Early June: 18 records, Late June: 15 records, Aug: 23 records. When we add the next records we want to remove the Early June records. However adding new data isn't totally regular. My second thought is to have an "oldness" field for each record and before adding a new data set, increment all of the "oldness" fields by 1, then removing all the data that has "oldness" of 3.

I'm worried that this is not the optimal way to do this, but I'm not sure it matters since it's a fairly small database that is updated infrequently. That said, I'm certainly open to suggestions for a better algorithm to accomplish this.

Solution

  • With a little help from user937146 I think I've got the solution. Basically 

    $query = "SELECT MIN(timestamp) FROM new_items WHERE type = 'Book' ";

    Then it's just a matter of taking that timestamp, adding an hour (since the data can take more than a second to upload, probably one minute would be sufficient) and then deleting everything from the database older than that timestamp.