There are plenty of discussions on the benefits and drawbacks of using hashes as primary keys, but in my case I have a database of webpages that I need to look up by address, and these addresses are longer than the maximum text index length. Also, it's quite the performance hit to index on varchar.

Until recently I was using MD5 checksums to look up addresses due to the speed of the function and a limited need for protection against hash collisions. But this specific application has the potential for the submission of "database-malicious URLs" that are invalid*, but crafted with the same checksum as valid URLs.

Is there a big performance hit switching from a primary key using 128-bit MD5 to a primary key of SHA-224+ (the smallest "unbroken" SHA-based hash I can find)? Should I index these as byte arrays or chars? Or is there another solution if I need reverse lookup AND collision security?

My DBMS is MySQL 5.7, but I would consider switching if there's an amazing solution in another DBMS with similar resource requirements that is freely available.

*Invalid as in technically resolvable, but not a "real" URL, i.e. http://example.com/reallyLongStringThatYieldsAHashCollisionWithARealURL

  • What if you store first few characters of url (as a new column) in addition to md5 ? It will make it practically impossible to generate malicious URL.
    – a1ex07
    Commented Oct 5, 2016 at 16:10
  • If I append the first 8 characters of the domain name and the first 8 characters of the path, that takes me from a 16-byte MD5 hash to a 32-byte string, at which point I may as well use the 32-byte SHA-256 or the 28-byte SHA-224, if my math is correct.
    – ndm13
    Commented Oct 5, 2016 at 16:24
  • What database? A url is too long to be indexed?
    – paparazzo
    Commented Oct 5, 2016 at 16:52
  • 1
    @Paparazzi Yes, a URL is too long to be indexed. A URL is essentially a varchar(2000) based on web browser expectations (see this question), and to maintain compatibility I need to store that theoretical maximum. According to this answer the maximum number of characters indexed by a varchar is 766. And yes, I have come across URLs that are longer than 766 characters.
    – ndm13
    Commented Oct 5, 2016 at 16:56
  • 1
    Oh, so the database is MSSQL. The answer would be database dependent. That is why I ask what database?
    – paparazzo
    Commented Oct 5, 2016 at 16:58


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.