A better way to check for duplicate before INSERT?

Also, indexing any column you join on or where on is a massive speed increase.

How the heck did you do that? It would have taken me longer to type your response than had elapsed since I posted the Q!

I'm not 100% sure REPLACE will work for me. I don't know the primary key ('id') before the INSERT, and none of the columns need to (or can be) UNIQUE.

I may have 10 attempts at (different times / or dates) to complete 'this' and I want to log them all, not just the completed one.

Thanks,

Tony

TunaMaxx,
>  duplicate entries show up in subsequent log files
what makes it duplicate?  define an index around that

arantius:

Does that mean that the combination of values stored in 'this, that, theother' is what is unique, or would that make each of those columns unique individually?

The log files I am parsing are from digital printers and the postcript RIP's that prep the art for printing. Seemingly, there shouldn't be too many duplicate entries, but in reality there are.

Filename:
Although every file that gets RIP'd *should* have a unique name, many times the same file will sent through again at a later date with slight modifications. Not everyone that (re)sends the files will alter the name. *I* know that everyone should be using unique names. I have set the RIP's to ensure that every OUTPUT name is unique (firstfile.000001.rtl, firstfile.000002.rtl, nextfile.000003.rtl, etc)) However, getting people to 'go out of their way' and add a unique identifier to the INPUT filename is a much larger challenge. It would be MUCH less headache to throw bigger hardware at the mySQL server to more quickly burn through the current SELECT / INSERT routine! ;)  

Time / Date:
This might be a candidate for a UNIQUE column. The problem is one set of logs has two sources, and the other set has three. Chances may be slim, but theoretically two operations could take place at the same time, and then I'd only have one of them logged. (None of the logs record micro-seconds, and I can't do anything about that) I have three years and millions of lines of logs to look through for duplicate timestamps. I wonder if or how many I'll find?

In reality, my question just might be one of research than anything else. After I have finished with converting the three years worth of logs, the daily maintenance will only be a few thousand lines. The code excecution time will sub-second versus *sub* sub-second!

Thanks,

Tony

Hmmm...

I wonder if this is what I am after?

http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html
"INSERT INTO tbl_temp2 (fld_id)
    SELECT tbl_temp1.fld_order_id
    FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;"

I'm not at work to even begin to test it, but maybe I would use something like:

INSERT INTO x (this, that, theother) VALUES ('$this', '$that', '$theother') SELECT * FROM x WHERE this <> '$this' AND that <> '$that' AND theother <> '$theother'

Something about that just doesn't look right... :)

Oops!

From the mysql.com link above:

"Currently, you cannot insert into a table and select from the same table in a subquery."

You should find a unique key or primary key first if it is possible, you mentioned filename and date/time. These 2 fields may not be unique, but would the combined of (filename and date/time) be unique? If it is still not unique, then add more fields until you can form a unique group of fields.

You did a SELECT to compare the value to insert with the current data in the table, I guess the combined of all these will form a unique key. But, if the number of fields is big, then it is no longer feasible, MySQL allows only about max 1024 bytes in a key.

With a UNIQUE KEY or PRIMAY KEY, you can then use INSERT IGNORE or REPLACE to insert the new rows.

Alternative method:
Since the duplicates forms about 5% of the rows, maybe you can just leave them. When you do your select add the DISTINCT keyword to remove the duplicates.
SELECT DISTINCT * FROM tablename.

If you want you can also do some housekeeping once in a while:
CREATE TABLE temp ( ... );
INSERT INTO temp SELECT DISTINCT * FROM yourtable;
TRUNCATE yourtable;
INSERT INTO yourtable SELECT * FROM temp;
DROP TABLE temp;
Can you get teh idea?

This shoule be more efficient than your SELECT before INSERT, I guess you have no INDEX on the table at all, so the SELECT will take quite some time.

An even better method if you are using MyISAM tables.
Create 2 tables exactly the same structure, then make a MERGE table consisting of these 2 tables.
Always insert into 1 of the MyISAM table, let say table1
Everyday, do this once, preferabley after midnight.
INSERT INTO table2 SELECT DISTINCT * FROM table1 WHERE datefield < current_date();
DELETE table2 WHERE datefield < current_date();

The idea is to move all the rows from previuos day from table1 to table2, and delete them from table1.
You will still need to use DISTICT in all your SELECT though, and you can SELECT from the combined MERGE table.