views:

32

answers:

2

I have frequent updates to a user table that simply sets the last seen time of a user, and I was wondering whether there is a simple way to defer them and group them into a single query after a short timeout (5 minutes or so). This would reduce queries on my user database quite a lot.

+1  A: 

If you do a UPDATE LOW_PRIORITY table ... you will make sure it will only execute your update when it's not doing anything else. Besides that I don't think there are much options inside MySQL.

Also, is it causing problems now or are you simply optimizing something that isn't a problem? Personally, if I would batch updates like these I would simply insert all the IDs in memcached and use a cronjob to update every 5 minutes.

WoLpH
I quite like your caching method, I'll try it out. Basically I keep my data in a cache (Pear Cache_Lite in my case) and then I have a regular writeback service.
cdecker
+1  A: 

Wolph's suggestion should do the trick. Also possible is to create a second table without any indices on it and insert all your data into that table. It can even be an in memory table. Then you an do a periodic INSERT INTO table1 SELECT * FROM TABLE2 ON DUPLICATE KEY UPDATE ... to transfer to the main table.

e4c5