There's been a thirst for this kind of thing for sometime but possibly you're looking for a cheaper option? Since 5.5 there's some incarnation of an audit plugin which can be extended for your own needs which should allow you to perform some persistence of the results with either a log file which could be processed into a table before you run your daily query?
http://dev.mysql.com/doc/refman/5.5/en/writing-audit-plugins.html http://karlssonondatabases.blogspot.co.uk/2010/03/mysql-audit-plugin-api.html HTH Andy On Fri, May 31, 2013 at 12:05 PM, Carsten Pedersen <cars...@bitbybit.dk>wrote: > Based on the little information available, I would make a lookup field > consisting of tablename and primary keys. > > (although I still believe that storing this information in the database in > the first place is probably the wrong approach, but to each his own) > > / Carsten > > > On 31-05-2013 12:58, Neil Tompkins wrote: > >> The kind of look ups will be trying to diagnose when and by who applied >> a update. So the primary key of the audit is important. My question is >> for performance, should the primary key be stored as a indexed field >> like I mentioned before, or should I have a actual individual field per >> primary key >> >> >> On Fri, May 31, 2013 at 11:03 AM, Carsten Pedersen <cars...@bitbybit.dk >> <mailto:cars...@bitbybit.dk>> wrote: >> >> Again: Unless you can give some idea as to the kind of lookups you >> will be performing (which fields? Temporal values? etc.), it is >> impossible to give advice on the table structure. I wouldn't blame >> anyone for not being able to do so; saving data for debugging will >> always be a moving target and almost by definition you don't know >> today what you'll be looking for tomorrow. >> >> That's why I think that using CSV tables _the contents of which can >> subsequently be analyzed using any of a number of text file >> processing tools_ may indeed be your best initial option. >> >> On UUIDs vs. INTs: (1) Please do yourself a favor and read up on how >> UUIDs are generated. If it's the same server that generates all the >> UUIDs, you won't get a lot of uniqueness for the amount of space >> you'll be using for your data and index; (2) Please do the math of >> just how many inserts you can do per second over the next 1.000 >> years if you use a longint auto-increment field for your PK. >> >> / Carsten >> >> On 31-05-2013 11 <tel:31-05-2013%2011>:14, Neil Tompkins wrote: >> >> Thanks for your response. We expect to use the Audit log when >> looking into >> exceptions and/or any need to debug table updates. I don't >> think a CSV >> table would be sufficient as we are wanting to use a interface >> to query >> this data at least on a daily basis if not weekly. >> >> I use UUID because we have currently 54 tables, of which >> probably 30 will >> be audited. So a INT PK wouldn't work because of the number of >> updates we >> are applying. >> >> >> On Fri, May 31, 2013 at 9:58 AM, Carsten Pedersen >> <cars...@bitbybit.dk <mailto:cars...@bitbybit.dk>>**wrote: >> >> >> On 30-05-2013 09:27, Neil Tompkins wrote: >> >> Hi, >> >> I've created a Audit table which tracks any changed >> fields for multiple >> tables. In my Audit table I'm using a UUID for the >> primary key. However >> I >> need to have a reference back to the primary key(s) of >> the table audited. >> >> At the moment I've a VARCHAR field which stores primary >> keys like >> >> 1 >> 1|2013-05-29 >> 2|2013-05-29 >> 2 >> 3 >> 1|2|2 >> etc >> >> Is this the best approach, or should I have a individual >> field in the >> audit >> table for all primary keys. At the moment I think the >> max number of >> primary keys on any given table is 3 >> >> Thanks >> Neil >> >> >> First you need to ask yourself how you expect to use the >> table in the >> future. Will you be looking up the data on a regular basis? >> Or will lookups >> only be something you will do in exceptional situtions? >> >> What is the intended goal of having a UUID for the primary >> key rather >> than, say, an integer - or having no PK at all? >> >> My immediate thought when reading this was "why even store >> that data in a >> table?" - if it's a simple log, use a log file. Especially >> if you don't >> know how you intend to search for data later on. There are >> many tools that >> are far superior to SQL when it comes to searching for text >> strings. You >> could even consider having a CSV table, which will give you >> an SQL >> interface to said text file. >> >> / Carsten >> >> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: http://lists.mysql.com/mysql >> >> >> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: http://lists.mysql.com/mysql >> >> >> > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql > >
