Help: rejecting duplicate rows

[g_chime]

I'm sorry, I had a couple of typos in my original post.
Here is the corrected version:

How can I make MySQL reject duplicate rows when inserting?

The table is quite large: 100+ columns and millions of rows.

MySQL doesn't allow me to create a UNIQUE INDEX for all the columns
(16 columns is the max.)

The brute force method (selecting the new candidate row and inserting
only when not found) is way too slow.

Any help is appreciated.

[Ivan Nikolov]

g_*****@yahoo.com wrote:

I'm sorry, I had a couple of typos in my original post.
Here is the corrected version:

How can I make MySQL reject duplicate rows when inserting?

The table is quite large: 100+ columns and millions of rows.

MySQL doesn't allow me to create a UNIQUE INDEX for all the columns
(16 columns is the max.)

The brute force method (selecting the new candidate row and inserting
only when not found) is way too slow.

Any help is appreciated.

Try INSERT IGNORE INTO ....
this will omit conflicting new records where there is a duplication on
an unique key

--

[Gordon Burditt]

>> I'm sorry, I had a couple of typos in my original post.

Here is the corrected version:

How can I make MySQL reject duplicate rows when inserting?

The table is quite large: 100+ columns and millions of rows.

MySQL doesn't allow me to create a UNIQUE INDEX for all the columns
(16 columns is the max.)

The brute force method (selecting the new candidate row and inserting
only when not found) is way too slow.

Any help is appreciated.

Try INSERT IGNORE INTO ....
this will omit conflicting new records where there is a duplication on
an unique key

But he can't CREATE a unique key, since he needs a unique index over
more than 16 columns, and MySQL won't let him do that.

Just how many columns would the unique index need to cover? Is
there a possibility of combining some of them?

Example: if you've got columns a01 through a99, and you need the unique
index over all of them, add another column which is created from:

concat(a01, '|', a02, '|', ... , '|', a99)

and create a unique index on THAT. Of course, this is a pain to
do on all the inserts. This does presume that the values of the columns
a01 .. a99 can't have '|' as part of the value. As I recall, you can't
have an index cover more than 255 characters, and that might present a
problem in this case also.

Gordon L. Burditt