Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm fairly new to working with databases, but I've currently have a webCrawler that I've written in C# using MYSQL database. The crawler is frequently writing and deleting records from the database as sites are scraped.

Each record has primary key, which is MD5 checksum for the URL, to insure that no table has two duplicate entries.

Currently is it good practice to do a check on table before inserting the database to see if there is a duplicate. I.e. two operations on the database.

Or is it sufficient to add the record and let the database gracefully fail to add it.

The same question is relevant for other operations like deleting etc.

At the moment I'm trying to operate on 1000's of records a minute from a single client (multiple connections from that client). Does the answer change knowing that level of database activity.

Also it fairly frequently that there will be duplicate. and the add code will be skipped. Say every ten adds, there is one new record.

share|improve this question
    
The md5 of the Url is a horrible key. You will end up with collision problems. I guarantee it. –  mrdenny Mar 14 at 22:38
    
@MrDenny, I was reading various articles which suggested that accidental collisions were are improbable as winning the intergalactic lottery. Have you got any sources which highlight collision rates / better way to store long urls for search and look up? I've not found a collision in 5.4 millions urls yet. –  Gregory William Bryant Mar 15 at 9:27

1 Answer 1

For inserts, you can use INSERT ... ON DUPLICATE KEY UPDATE .... This lets you update certain fields if primary key is already used.

The syntax would be something like:

INSERT INTO `table` 
(`id`, `column1`, `column2`, `column3`) 
VALUES ('key', 'data1', 'data2', 'data3') 
ON DUPLICATE KEY UPDATE 
`column1`= values(`column1`), 
`column2` = values(`column2`), 
`column3` = values(`column3`);

More information: http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html

Also if you are running asynchronous inserts, check out INSERT DELAYED.

More info: http://dev.mysql.com/doc/refman/5.6/en/insert-delayed.html

share|improve this answer
    
Thanks for the tips - can I conclude that its best practice to not check first? –  Gregory William Bryant Mar 15 at 9:27
    
Checking by performing a query every time is costly. If you don't have much data sometimes it's effective to cache required data in an array or something and check for duplicates in your programming language, but if you have a lot of data it will not be memory efficient. –  edvinas.me Mar 15 at 13:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.