List:General Discussion« Previous MessageNext Message »
From:Neil Tompkins Date:May 31 2013 10:58am
Subject:Re: Audit Table storage for Primary Key(s)
View as plain text  
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
> 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: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 <carsten@stripped
>> >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
>
>

Thread
Audit Table storage for Primary Key(s)Neil Tompkins30 May
  • Fwd: Audit Table storage for Primary Key(s)Neil Tompkins31 May
    • RE: Audit Table storage for Primary Key(s)Jason Trebilcock31 May
  • Re: Audit Table storage for Primary Key(s)Carsten Pedersen31 May
    • Re: Audit Table storage for Primary Key(s)Neil Tompkins31 May
      • Re: Audit Table storage for Primary Key(s)Carsten Pedersen31 May
        • Re: Audit Table storage for Primary Key(s)Neil Tompkins31 May
          • Re: Audit Table storage for Primary Key(s)Carsten Pedersen31 May
            • Re: Audit Table storage for Primary Key(s)Andrew Moore31 May
            • RE: Audit Table storage for Primary Key(s)Rick James31 May
              • Re: Audit Table storage for Primary Key(s)Andrew Moore31 May