To be honest, this sounds like more of a filesystem thing, given that you only ever need to select the full set of an individual user. Just build up an FS structure with one file per user.
On Wed, Jul 1, 2009 at 7:57 PM, Marcus Bointon <mar...@synchromedia.co.uk>wrote: > I need to log fairly large numbers of historical events relating to mailing > list activity on a per-recipient basis, a kind of audit trail if you like. > So for a given user, I might log the fact that they subscribed to a list, > that they were uploaded by someone, that they were sent a message, that they > unsubscribed etc. For the most part this is write-only and is only ever read > very rarely, but when I do, it will be to retrieve the details of a single > user, and all I need is the whole history, not individual events. At present > I'm logging by appending to a text blob field for each recipient. This works > ok, but the append gets progressively slower over time as the append speed > is dependent on the length of the existing field, which I think makes for an > O(n^2) complexity overall, which is obviously not good. A typical choke > point is after a list upload when I need to batch-update records for > everyone on the list, which may be half a million or more records. > > I'm not bothered about current live data as that's not such a problem - > this is just about the historical data. > > I'm looking for a good alternative to this. So far I've looked at archive > tables for logging on a per-event basis (can't do per-user as it doesn't do > updates), however, with a large number of users and events, this would > rapidly grow by several million records per week, and I suspect searches > would become unusably slow (no index in that storage engine). I thought of > using memcachedb instead, however, unless I threw large numbers of servers > at it, I would simply be moving my bottleneck to whatever back-end > memcachedb uses (sqlite? bdb?) which is probably less efficient than MySQL > anyway. Partitioning isn't a great solution as that's mainly for improving > select performance. Keeping a file on disk for each user might work, but I > suspect that would become impractical as I have millions of users, and files > on disk is just a kind of inefficient home-brew database. > > So, any other ideas? > > Marcus > -- > Marcus Bointon > Synchromedia Limited: Creators of http://www.smartmessages.net/ > UK resellers of i...@hand CRM solutions > mar...@synchromedia.co.uk | http://www.synchromedia.co.uk/ > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be > > -- Celsius is based on water temperature. Fahrenheit is based on alcohol temperature. Ergo, Fahrenheit is better than Celsius. QED.
