I want to be sure I understand "INSERT IGNORE..." correctly before I start depending on it. Up until now, I have not been using any kind of key or unique index, since many of my tables are created automatically and, until now, it has been difficult for me to create a way to distinguish between the tables that would have multiple matching records and the tables that need to have only unique values. So now that I can start using keys, I have a few questions:
1) I've been using "SELECT FirstName, LastName, Birthdate FROM division.People WHERE FirstName = '$fname' AND LastName = '$lname' AND Birthdate = '$bday'" to check for preexisting records. This means before I inserted a record, I would select on specific fields and see if they matched the fields of the current record. If they did, I threw out the record I was going to enter, if there was not a match, I'd INSERT the new record. If I use "INSERT IGNORE...", MySQL will still have to verify that the new record does not match any old records. How much faster is it to do it that way than the way I was? I'd think the same routines to find matching data would be used. 2) Right now I'm creating an archival database to store older records in. In one of these archives, there are over 250,000 records. At this point, by doing things the "old way" (checking for a match, then inserting), it is now able to insert about 750 records in 10 minutes. Earlier, when it was only about 180,000 records, it was inserting at about 1,000 records in 10 minutes. So, first, how much of a speed up can I count on if I use "INSERT IGNORE" instead, and second, if I use "INSERT IGNORE DELAYED", will the program finish faster, leaving MySQL to catch up with the queued INSERT statements as it can? 3) While this has been stated on the mysql.com, it is not what I was originally taught by a friend and some books, so I want to be clear I understand this correctly before I depend on it. As I understand it, "INSERT IGNORE..." compares the data being inserted with the keys of all records in the table and will not insert it if it duplicates an existing multi-column key. The IGNORE basically tells MyQL to not generate an error message if the data I'm inserting is a duplicate, so I can use "INSERT IGNORE..." in a Perl program to be sure I'm not duplicating records and not getting error messages on it if the data is a duplicate. Is this correct? Thanks! Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
