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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 '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 '14 at 9:27

There are at least two drawback when using a query to check if URL exist:

  1. Sending an extra query to database, will include additional network delay to your overall response time and also more network traffic.
  2. no matter what value you insert, database will still check for duplicate key. therefore, the search on primary key is performed twice when two query is used.

Moreover, @mrdenny is right, there is 1% chance of collisions (with as few as 93,000 values). in order to avoid that, keep a copy of the URL in your table and write query as below:

SELECT * FROM myTable WHERE md5Val = MD5('') and url = '';

This will avoid collisions but your md5 value cannot be the primary key anymore. Therefore, I suggest to create table as below:

id int unsigned NOT NULL auto_increment,
url varchar(255) NOT NULL,
url_crc int unsigned NOT NULL DEFAULT 0,
PRIMARY KEY(id), INDEX index_url_crc(url_crc)

and you may also use a trigger to maintain the crc value

SET NEW.url_crc=crc32(NEW.url);
SET NEW.url_crc=crc32(NEW.url);

and now query your table like this

SELECT * FROM Urls WHERE url_crc = CRC32('') and url = '';

hope this is helpful.

share|improve this answer

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') 
`column1`= values(`column1`), 
`column2` = values(`column2`), 
`column3` = values(`column3`);

More information:

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

More info:

share|improve this answer
Thanks for the tips - can I conclude that its best practice to not check first? – Gregory William Bryant Mar 15 '14 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. – nmad Mar 15 '14 at 13:14

Your Answer


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.