Page 1 of 1

DB insertion best practices

Posted: Tue Sep 27, 2011 2:04 pm
by kc11
Hi everyone,

I am working on a script that parses a feed periodically for new records and then inserts them into a mysql DB via PDO. I would like to use some sort of efficient caching mechanism so that no duplicate records exist in the DB. Would it be best to insert everything and then delete the duplicates in the mysql table , or somehow cache my insert statement so that it inserts only new records. Perhaps mysql or PDO have some mechanism which can help with this. I would appreciate any directions on best practices for this problem.

Thanks in advance,

KC

Re: DB insertion best practices

Posted: Tue Sep 27, 2011 8:07 pm
by califdon
How are "new records" defined? If every record has a unique key field, you might just rely on the database, setting that field to "indexed unique" and trapping the errors. I would certainly prefer never entering the data, over detecting duplicates later.

Re: DB insertion best practices

Posted: Tue Sep 27, 2011 9:21 pm
by kc11
Hi Califdon,

By 'new' record, I mean a record with a unique key field, which has not already been inserted into the DB. I want to avoid duplicate records in the DB. I agree however that for efficiency's sake the best way is to only insert 'new' records.This is the way I've been going up until now, by using pdo so select a list of all records in the db and load them into an array. I then use array_diff to compare database contents to a list of recently parsed records, and select the new ones for insertion. Is there a better way?

Thanks,

KC

Re: DB insertion best practices

Posted: Tue Sep 27, 2011 9:59 pm
by califdon
Sure. That's what unique keys are for. Just create a unique index on the key field, then the database won't accept a record if one already exists with the same key value. You don't have to do anything with arrays. The only other thing you will want to do is to trap the error that is created, since it would be ugly to see a bunch of error messages and maybe hear a bunch of beeps! :)

Re: DB insertion best practices

Posted: Wed Sep 28, 2011 1:09 pm
by kc11
Thanks Califdon,

I'll read up on unique keys.

Best regards,

KC

Re: DB insertion best practices

Posted: Mon Oct 03, 2011 3:22 am
by VladSun

Re: DB insertion best practices

Posted: Sat Oct 29, 2011 2:35 am
by uday8486
Yes unique keyword is that purpose only, You can have a unique key and an error handler that what should happen if duplicate for unique key is found so that you do not end up with script termination.