Baron,

Just feedback - worked perfectly.
Thanks again!

-----Original Message-----
From: Esbach, Brandon
Sent: 31 August 2007 14:46
To: Baron Schwartz
Cc: MySQL User Group
Subject: RE: Two/more seperately unique columns in a table?

Hi, and thanks Baron;
I should have been a bit clearer on the bulk insert - I am using a bulk
insert statement, as you assumed.
I'll put this onto the db server and check, I think that's a more future
proof method. Will this affect any of my linked tables (linked via the
row's primary key(id))?

-----Original Message-----
From: Baron Schwartz [mailto:baron@xaprb.com]
Sent: 31 August 2007 14:28
To: Esbach, Brandon
Cc: MySQL User Group
Subject: Re: Two/more seperately unique columns in a table?

Hi,

Esbach, Brandon wrote:
> Hi all,
>
> I'm guessing this is a fairly common question, but I've drawn a blank
> so far with all workarounds possible.
> Here's the rough scenario:
> We have data streaming in constantly from various offsite locations.
> This comprises of several details: a session, a unit of that session,
> and a measurement of that unit.
> Data is captured and transferred on a timed process, so often data is
> repeated in the data transfer packets. This repeating is unavoidable
> as the software used to capture dumps this data for each measurement
> and each unit for the session it's working on.
>
> Due to the volume, a bulk update is done using "values()" with an
> insert statement.
>
> Unfortunately, often there are repeats of either session, unit, or
> measurement (as there could be new measurement or unit for the capture

> that needs to be associated to the session).
> The problem I've been experiencing is fairly straightforward (I hope):

> I have two, sometimes three columns in any given record that need to
> always be unique. This comprises an ID (the key column), and one
> (depending on the table, sometimes two) GUIDs which should be unique
> at all times for the entire table.
>
> I've tried setting the additional columns to be a primary key (which
> in turn sets them up to be "unique" when viewed under "Schema Indices"

> on the MySQL Administrator tool); however this does not give an error
> (or fail silently with "insert ignore") when I insert a duplicate -
> mySQL seems quite happy to add the duplicate record.
>
> At the moment, I'm running a process in the beginning which simply
> gathers all guids from the database and compares them as it runs
> through the data (then adds new ones as it runs).. This is hardly
> reliable, and also means starting the service would take several hours

> to gather the existing guids at current data levels... almost
> frightening to think, what will end up happening as the data expands.

It sounds like you need a separate primary key and unique index:

create table t (
id int not null,
guid char(32) not null,
unique key (guid),
primary key(id)
);

Then you can do REPLACE or IGNORE with the LOAD DATA INFILE. I can't
tell if you are actually using LOAD DATA INFILE or if your "bulk load"
is a big INSERT statement. If you're using an INSERT with multiple
VALUES() sections, you can also use ON DUPLICATE KEY UPDATE.

I agree the current strategy won't hold up well over time.

Baron

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=e...lectronics.com