I keep some temporary data in a memory table. I only need the 20 most recent entries and would prefer the data is always be on the heap. How should i accomplish this? i am sure theres nothing i can do about the memory table but how should i handle entries tables? should i add a autoincrease key and delete the oldest whenever i want to push a new value in?
A:
Appending to the 20-entry table and removing the eldest element (i.e. the one with the minimum ID?) is possible. However, note that this will fragment the table.
That's OK so long as you run OPTIMIZE every once in a while.
A different way would be to pre-allocate 20 entries and keep a separate counter of which entry is the latest. Then instead of insert/delete, you would update the item ID based on the counter, which you would then increment (mod 20 + 1) and store again.
However note that both of these models work only under a "single-threaded" model. If multiple threads are running on the table it's possible that they'll conflict.
If the counter is in program memory, shared by threads but guarded properly, that will be both thread-safe and efficient.
Jason Cohen
2009-02-15 20:43:26
how do you guard mysql tables? doesnt mysql do this for you?also are you suggesting having a table with a single column which holds a single row which is the index to write in?
acidzombie24
2009-02-15 21:03:28
Regarding your fragemntation statement: Isn't that irrelevant in the face of a maximum of 20 rows?
Tomalak
2009-02-15 23:07:59
@acid: MySQL ensures data integrity, but you're doing two operations: insert and delete. How do you decide which to delete? Select for the minimum ID of the table and remove? What if two threads do that at the same time? They will pick the same row. MySQL cannot prevent that.
Jason Cohen
2009-02-16 00:51:03
@Tomalak: It's not irrelevant after e.g. 10,000 operations.
Jason Cohen
2009-02-16 00:51:42
@Jason - mysql can prevent that, although it takes the overhead of a select statement. http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html
yaauie
2009-03-11 06:59:56
+1
A:
Could you please describe in more detail, what you are trying to do? I don't see why you want to keep the most recent data in an additional table when you can just use a SELECT with descending order and a LIMIT 20. If the SELECT query is too expensive then just cache the result using memcached or similar and clear the cache every time a new data is inserted.
If the additional table is really necessary there are several ways to prune old data from the table. Either you fetch the id of the 20th recent data (again descending order and LIMIT 19,1 and delete everything that has a smaller id (in case you have an auto increment index, timestamp, etc.) or you SELECT COUNT(*) and then do a DELETE with ascending order and a LIMIT (all items - 20). This could be packed into a cronjob that runs every several minutes.
But I would really recommend using a cache and looking at the table definition. With a decent index there shouldn't be any problems.
Uwe Mesecke
2009-03-17 00:40:37