CODEWITHSUNDEEP
mysql
natli
natli

Reputation: 3822

How to properly clean up a table

In order to determine how often some object has been used, I use a table with the following fields;

id - objectID - timestamp

Every time an object is used, it's ID and time() are added in. This allows me to determine how often an object has been used in the last hour/minute/second etc.

After one hour, the row is useless (I'm not checking above one hour). However, it is my understanding that it is unwise to simply delete the row, because it may mess up the primary key (auto_increment ID).

So I added a field called "active". Prior to checking how often an object has been used I loop over all WHERE active=1 and set it to 0 if more than 1 hour has passed. I don't think this would give any concurrency problems between multiple users, but this leaves me with alot of unused data.

Now I'm thinking that maybe it's best to, prior to inserting new usage data, check if there is a field with active=0 and then rather than inserting a new row, update that one with the new data, and set active to 1 again. However, this would require table locking to prevent multiple clients from updating the same row.

Can anyone shed some more light on this, please?

Upvotes: 1

Views: 534

Answers (2)

Michael Slade
Michael Slade

Reputation: 13877

I've never heard anywhere that deleting rows messes up primary keys.

Are you perhaps attempting to ensure that the id values automatically assigned by auto_increment match those of another table? This is not necessary - you can simply use an INTEGER PRIMARY KEY as the id column and assign the values explicitly.

Upvotes: 2

Stelian Matei
Stelian Matei

Reputation: 11623

You could execute an update query that match all rows older than 1 hour.

UPDATE table SET active=0 WHERE timestamp < now() - interval 1 hour

Upvotes: 1

Related Questions