Search and write, or write and recover?

[Roy Epperson]

The problem: I need to generate a 'unique string' for each row in a
table. I already use auto_increment for system dependencies between tables.

What is the best approach one of these or another?

After generating a candidate 'unique string' the two strategies that
came to mind are:

1. to then search the table's column to see if it is already assigned;
locking the table for write while searching and writing the new row, or

2. set the column to UNIQUE when defining the table. Just go ahead and
write the new row if you get a "non-unique" exception, generate another
'unique string' and try again.

I've tried both on a small XP laptop and get "lock timeout exceptions"
rather quickly using #1. But replace those with lots of re-writes when
there starts to get "collisions" of 'unique string's.

Any recommendations on approaches?

Thanks in advance,
Roy

[Bill Karwin]

Roy Epperson wrote:

I've tried both on a small XP laptop and get "lock timeout exceptions"
rather quickly using #1. But replace those with lots of re-writes when
there starts to get "collisions" of 'unique string's.

Any recommendations on approaches?

Use a better algorithm for generating unique strings. There are hashing
algorithms that are quite unlikely to generate collisions over a normal
sized set of data.

Try MD5() or SHA1(), which are both referenced with builtin functions in
MySQL.

http://dev.mysql.com/doc/refman/5.0/...functions.html

Regards,
Bill K.

[onedbguru]

You are more likely to succeed if you use the data itself as a unique
key. If you are using a primary key, that would be considered a
"unique string". If this is coming in from a web server and you need
an additional unique id, you can always use the host generated
UNIQUE_ID.

using an auto-generated key on multiple tables can be very dangereous
if you do not have the proper referential integrity configured. In most
cases I have seen, the auto-incremented field is mostly useless on
child tables. That being said, the field should exist in both parent
and child tables, but should only be auto-increment in the parent - and
value must exist in Parent before inserting into a child -- know as a
Foreign Key.

PK example:

username -- should be the only part of a PK and should be unique by
itself - you can only have one username "myusername" You can have an
auto-increment userid#, but should NOT be a part of this PK.

Depending on what this is for, you should more than likely require the
services of a DBA that knows what he is doing... The great thing about
databases is that "anyone" can build one. The worst thing about
databases is that "anyone" can build one. The problem with most
database apps is that "anyone" built it.

You will never know how your db/app is going to perform until after
deployment and NO amount of hardware/file tuning will EVER correct a
bad database design. "But it worked in test (with only 50 rows) now,
with 1M rows performance really stinks"... DUH!

[Roy Epperson]

Thanks for the pointer. However, the unique key is not based on any
data in the table; just "picked out of the air". I don't understand how
MD5 or SHA1 might be used to generate a unique key without some input
data that is varing?
Bill Karwin wrote:

Roy Epperson wrote:

I've tried both on a small XP laptop and get "lock timeout exceptions"
rather quickly using #1. But replace those with lots of re-writes when
there starts to get "collisions" of 'unique string's.

Any recommendations on approaches?

Use a better algorithm for generating unique strings. There are hashing
algorithms that are quite unlikely to generate collisions over a normal
sized set of data.

Try MD5() or SHA1(), which are both referenced with builtin functions in
MySQL.

http://dev.mysql.com/doc/refman/5.0/...functions.html

Regards,
Bill K.

[Roy Epperson]

Thanks for the comments.. I understand your points on keys. My "unique
key" is random set of non-whitespace to externally identify some info in
the db whose contents are not yet known when the record to the
information is created.
on*******@firstdbasource.com wrote:

You are more likely to succeed if you use the data itself as a unique
key. If you are using a primary key, that would be considered a
"unique string". If this is coming in from a web server and you need
an additional unique id, you can always use the host generated
UNIQUE_ID.

using an auto-generated key on multiple tables can be very dangereous
if you do not have the proper referential integrity configured. In most
cases I have seen, the auto-incremented field is mostly useless on
child tables. That being said, the field should exist in both parent
and child tables, but should only be auto-increment in the parent - and
value must exist in Parent before inserting into a child -- know as a
Foreign Key.

PK example:

username -- should be the only part of a PK and should be unique by
itself - you can only have one username "myusername" You can have an
auto-increment userid#, but should NOT be a part of this PK.

Depending on what this is for, you should more than likely require the
services of a DBA that knows what he is doing... The great thing about
databases is that "anyone" can build one. The worst thing about
databases is that "anyone" can build one. The problem with most
database apps is that "anyone" built it.

You will never know how your db/app is going to perform until after
deployment and NO amount of hardware/file tuning will EVER correct a
bad database design. "But it worked in test (with only 50 rows) now,
with 1M rows performance really stinks"... DUH!

[Bill Karwin]

Roy Epperson wrote:

Thanks for the pointer. However, the unique key is not based on any
data in the table; just "picked out of the air". I don't understand how
MD5 or SHA1 might be used to generate a unique key without some input
data that is varing?

Aha, thanks, I misunderstood what you were doing.

You might want to check out the UUID() function, which is designed to
generate a pseudo-random 128-bit number as a hex string, with a very low
probability of collisions.

http://dev.mysql.com/doc/refman/5.0/...functions.html

Regards,
Bill K.

[Roy Epperson]

Better pointer!! Thanks Bill. I never thought to look there..

Bill Karwin wrote:

Roy Epperson wrote:

Thanks for the pointer. However, the unique key is not based on any
data in the table; just "picked out of the air". I don't understand
how MD5 or SHA1 might be used to generate a unique key without some
input data that is varing?

Aha, thanks, I misunderstood what you were doing.

You might want to check out the UUID() function, which is designed to
generate a pseudo-random 128-bit number as a hex string, with a very low
probability of collisions.

http://dev.mysql.com/doc/refman/5.0/...functions.html

Regards,
Bill K.