Re: Audit Table storage for Primary Key(s)

[Andrew Moore]

Ah-ha, excuse my earlier response, I was under the impression you were
trying to track schema changes etc.

A


On Fri, May 31, 2013 at 7:54 PM, Rick James  wrote:

> UUID PRIMARY KEY (or even secondary index) --
> Once the table gets big enough (bigger than RAM cache), each row INSERTed
> (or SELECTed) will be a disk hit.  (Rule of Thumb:  only 100 hits/sec.)
>  This is because _random_ keys (like UUID) make caching useless.  Actually,
> the slowdown will be gradual.  For example, once the table is 5 times as
> big as the cache, 80% (1-1/5) of the INSERTs/SELECTs will hit disk.
> Bottom line -- Avoid UUIDs in huge tables, if at all possible.
>  (Exception:  The bits in type-1 UUIDs can be rearranged to be roughly
> chronological.)
>
> BIGINT -- You cannot possibly hit its max with any existing hardware.
>
> MyISAM -- PRIMARY KEY is just another secondary index.  Secondary indexes
> are separate BTrees.
>
> InnoDB -- PRIMARY KEY and data coexist in the same BTree.  Secondary
> indexes are separate BTrees.
>
> So, assuming this "audit" table will be huge (too big to be cached), you
> need to carefully consider every index, both for writing and for reading.
>
> You mentioned that you might audit 50 tables?  An index that starts with
> table_name would be inserting/selecting in 50 spots.  If the second part of
> the index is something 'chronological', such as an AUTO_INCREMENT or
> TIMESTAMP, then there would be 50 "hot spots" in the index.  This is quite
> efficient.  INDEX(table_name, UUID) would be bad because of the randomness.
>
> InnoDB may be the preferred engine, even though the footprint is bigger.
>  This is because careful design of the PK could lead to INSERTs into hot
> spot(s), plus SELECTs being able to take advantage of locality of
> reference.  With PRIMARY KEY(table_name, ...), and SELECT .. WHERE
> tablename='...', InnoDB will find all the rows together (fewer disk hits);
> MyISAM will find the data scattered (more disk hits, hence slower).
>
> Another aspect...  Would your SELECTs say "WHERE ... AND timestamp
> BETWEEN..." ?  And, would you _usually_ query _recent_ times?  If so, there
> could be a boost from doing both of these
> ** PARTITION BY RANGE(TO_DAYS(timestamp))
> ** Move timestamp to the _end_ of any indexes that it is in.
>
> I would be happy to discuss these principles further.  To be able to
> discuss more specifically, please provide
> ** Your tentative SHOW CREATE TABLE
> ** how big you plan for the table to become (#rows or GB),
> ** how much RAM you have
>
> > -Original Message-
> > From: Carsten Pedersen [mailto:cars...@bitbybit.dk]
> > Sent: Friday, May 31, 2013 4:05 AM
> > Cc: [MySQL]
> > Subject: Re: Audit Table storage for Primary Key(s)
> >
> > 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
> > > 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 

RE: Audit Table storage for Primary Key(s)

[Jason Trebilcock]

I'll ask the dumb question.

Why not create individual history tables corresponding to your 'main'
tables? So, if you have an 'address' table, then the original record could
be written to an 'address_his' table via an update or delete trigger
(depending on whether you allow deletions or not) when a change is
made...and the updated address record would be in the 'address' table. The
address_his table would really only need two additional fields to track your
data - a user field and a journal date/time.

Not sure how you're planning on writing to the changes to your audit table,
but this would allow the database to do the work instead of having to write
application code to do it.

Note: This is based on how I see things for the current application where I
work. Doesn't mean that it's right or wrong...it just works for us.

-Original Message-
From: Neil Tompkins [mailto:neil.tompk...@googlemail.com] 
Sent: Friday, May 31, 2013 3:43 AM
To: [MySQL]
Subject: Fwd: Audit Table storage for Primary Key(s)

Any advice anyone ?

-- Forwarded message --
From: Neil Tompkins 
Date: Thu, May 30, 2013 at 8:27 AM
Subject: Audit Table storage for Primary Key(s)
To: "[MySQL]" 


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


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: Audit Table storage for Primary Key(s)

[Rick James]

UUID PRIMARY KEY (or even secondary index) -- 
Once the table gets big enough (bigger than RAM cache), each row INSERTed (or 
SELECTed) will be a disk hit.  (Rule of Thumb:  only 100 hits/sec.)  This is 
because _random_ keys (like UUID) make caching useless.  Actually, the slowdown 
will be gradual.  For example, once the table is 5 times as big as the cache, 
80% (1-1/5) of the INSERTs/SELECTs will hit disk.
Bottom line -- Avoid UUIDs in huge tables, if at all possible.  (Exception:  
The bits in type-1 UUIDs can be rearranged to be roughly chronological.)

BIGINT -- You cannot possibly hit its max with any existing hardware.

MyISAM -- PRIMARY KEY is just another secondary index.  Secondary indexes are 
separate BTrees.

InnoDB -- PRIMARY KEY and data coexist in the same BTree.  Secondary indexes 
are separate BTrees.

So, assuming this "audit" table will be huge (too big to be cached), you need 
to carefully consider every index, both for writing and for reading.

You mentioned that you might audit 50 tables?  An index that starts with 
table_name would be inserting/selecting in 50 spots.  If the second part of the 
index is something 'chronological', such as an AUTO_INCREMENT or TIMESTAMP, 
then there would be 50 "hot spots" in the index.  This is quite efficient.  
INDEX(table_name, UUID) would be bad because of the randomness.

InnoDB may be the preferred engine, even though the footprint is bigger.  This 
is because careful design of the PK could lead to INSERTs into hot spot(s), 
plus SELECTs being able to take advantage of locality of reference.  With 
PRIMARY KEY(table_name, ...), and SELECT .. WHERE tablename='...', InnoDB will 
find all the rows together (fewer disk hits); MyISAM will find the data 
scattered (more disk hits, hence slower).

Another aspect...  Would your SELECTs say "WHERE ... AND timestamp BETWEEN..." 
?  And, would you _usually_ query _recent_ times?  If so, there could be a 
boost from doing both of these
** PARTITION BY RANGE(TO_DAYS(timestamp))
** Move timestamp to the _end_ of any indexes that it is in.

I would be happy to discuss these principles further.  To be able to discuss 
more specifically, please provide
** Your tentative SHOW CREATE TABLE
** how big you plan for the table to become (#rows or GB),
** how much RAM you have

> -Original Message-
> From: Carsten Pedersen [mailto:cars...@bitbybit.dk]
> Sent: Friday, May 31, 2013 4:05 AM
> Cc: [MySQL]
> Subject: Re: Audit Table storage for Primary Key(s)
> 
> 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
> > 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 :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
> >   

Re: Audit Table storage for Primary Key(s)

[Andrew Moore]

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 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 > > 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
>> 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
>

Re: Audit Table storage for Primary Key(s)

[Carsten Pedersen]

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 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 :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
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

Re: Audit Table storage for Primary Key(s)

[Neil Tompkins]

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 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 > >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
>
>

Re: Audit Table storage for Primary Key(s)

[Carsten Pedersen]

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 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

Re: Audit Table storage for Primary Key(s)

[Neil Tompkins]

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 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
>
>

Re: Audit Table storage for Primary Key(s)

[Carsten Pedersen]

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