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
2010-03-17 02:19:18
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
2010-03-18 15:54:18
+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
2010-03-17 03:38:51