I am creating a table wich will contain user-provided URLs. I want those to be unique, so when the user gives me a URL I will first check if the URL exists and if so return the ID for the entry. If not create a new row with this URL.

Obviously I want this to be fast. What is the best option?

  • Make the actual URL a varchar that is UNIQUE and look by this url?
  • Make a hash of the URL and use it as a primary key of sort?
  • Other ideas?
  • Hope you don't mind. I removed the PS section. We'll let you know if it's not a good fit by closing and/or downvoting! – Derek Downey Jan 19 '12 at 17:19

I would definitely go with a hash of the url and make the hash a unique index. A hash has a fixed length, so you can use CHAR to specify the length of the column, which grants a slight performance boost over VARCHAR or TEXT.

But might I suggest using INSERT IGNORE instead of making two calls to the database? Something like:


This has the benefit of ignoring any duplicate errors that might arise from attempting to insert a duplicate hash, without first having to do a SELECT COUNT(*) query.

  • Your approach is more concise. +1 !!! – RolandoMySQLDBA Jan 19 '12 at 17:09
  • I need the ID of the row, can I get it when doing insert ignore? – nute Jan 22 '12 at 9:20
  • Actually, do I still need a separate primary ID? Or should the hash be my primary key? Should I hash in MySQL, or in PHP? – nute Jan 22 '12 at 9:24
  • I just tested that SELECT LAST_INSERT_ID() will not return the ID on the value that was ignored (in the case of a duplicate). So you will either need to do a SELECT id FROM url WHERE urlHash='X', or drop the ambiguous primary key. It depends on your use-case. If this table actually has other columns other than the URL that you're indexing on, I'd recommend the first option and keep that auto-incrementing ID. – Derek Downey Jan 23 '12 at 15:47
  • Is there a way to make mysql fail silently if you try to write a duplicate or do you always have to query the table to check for the hash? Otherwise, I'm getting SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '211c2f38f92d7ad4380031dc533d376a' for key 'guid_hash' – codecowboy Mar 8 '12 at 10:45

Unless I'm missing something, you should just create a UNIQUE index of the type HASH. I don't see what adding your own hash and triggers would add? And have the field itself NOT NULL.

CREATE  TABLE `test`.`bla` (
  `text` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  • 2
    Interesting idea...though according to the docs, HASH isn't available for InnoDB: dev.mysql.com/doc/refman/5.5/en/create-index.html Oddly, it doesn't throw a warning when creating it like that. But the docs indicate that it will use BTREE (for innodb) silently, though the definition says HASH. – Derek Downey Jan 20 '12 at 14:56
  • Ah good point! Sounds like something that should be mentioned in whatever answer is approved or maybe in the question itself. In any case, this question sheds some more light on this: dba.stackexchange.com/questions/2817/… – Jannes Jan 20 '12 at 18:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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