0

I have a table in MySQL where arrive continuously data (these data have a timestamp). I'd like to create a FIFO Queue based on the size of the table. I mean, if I set for example 1GB as threshold and the data begin to be stored once I reach 1 GB of capacity I start to delete data in a fifo way. Anyone have any suggestions or have ever done anything similar?

Thank you

Sambalaganda
  • 1
  • I don't think there's anything that will automatically delete the older records. You can use Event Scheduler to periodically delete old records. – Barmar Feb 16 '22 at 16:58
  • A common strategy is to use partitions based on date. When you add a new partition for the next date, you remove the oldest partition. – Barmar Feb 16 '22 at 16:59

1 Answers1

0

I think you are confused on the definition of a FIFO queue. You don't describe any consumer of the queue, only deletions.

It sounds more like a least-recently-used cache, that holds up to 1GB of data, but the "oldest" data is overwritten as new data is inserted.

MySQL is neither a cache nor a queue. MySQL is a database. It is designed to store all the data you insert into it, durably. It has no feature to do automatic deletion.

I suggest you should use a cache server such as redis. It has the feature you are looking for, to evict data if the collection exceeds a fixed size.

See the post: Using Redis as an LRU cache

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828