Hi All,
I'm designing a database where it is required for each row in a table to
have a unique identifier accross a bunch of running MySQL servers on a
network.
I'm considering two options:
1. Have a two-column primary key, where the first column is a normal
auto incremented ID field, the second identifies the database and
contains a constant ID per server. Something like this:
CREATE TABLE MyTable (
ROWID int unsigned not null auto_increment,
DBID int unsigned not null,
AnyData varchar(10) not null,
PRIMARY KEY (ROWID, DBID)
);
INSERT INTO MyTable (DBID, AnyData) VALUES (8, 'Any text');
2. I would use a traditional one-column binary primary key populated by
the built-in uuid() fuction of MySQL. Like this:
CREATE TABLE MyTable (
ID binary(36) not null,
AnyData varchar(10) not null,
PRIMARY KEY (ID)
);
INSERT INTO MyTable (ID, AnyData) VALUES (uuid(), 'Any text');
In my view both solutions have their adventages and disadvantages. The
first is more optimal in storage space and speed, the second is easier
to maintain, administer and query.
And there is another aspect, which is needed to be tested, I guess...
How fast is the second solution when I execute complex queries based on
primary key relations? Does it pay to use the ease of the second
solution? Anyone has any experience in similar problems?
What is your opinion?
Thanks,
Daniel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
