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 <carsten@stripped>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
> 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:14, Neil Tompkins wrote:
>> Thanks for your response. We expect to use the Audit log when looking
>> 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 <carsten@stripped
>> On 30-05-2013 09:27, Neil Tompkins wrote:
>>>> 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.
>>>> need to have a reference back to the primary key(s) of the table
>>>> At the moment I've a VARCHAR field which stores primary keys like
>>>> Is this the best approach, or should I have a individual field in the
>>>> table for all primary keys. At the moment I think the max number of
>>>> primary keys on any given table is 3
>>>> 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
>>> 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
>>> 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