0

I am confused if in MySQL unique constraint is physical index or virtual index? And as unique constraint is defined on a column then, Is there any need to make a hash column also and defining index on hash column to speed up the process of queries considering that unique constraint column contains variable number of characters mostly above 40 characters and on average 50+?

And total records are 150+ million plus.

I have asked another question with details of hashes and its indexing plan. index on url or hashing considering RAM

Community
  • 1
  • 1
Rick James
  • 307
  • 1
  • 9

1 Answers1

1

Unique constraint pretty much maintains an index but technically its a constraint which requires an index. But if you want to adopt hashes just to make search faster than you need to make hash column as unique and not need to worry about collision if you choose a strong hash. 150+ million sample is not too big to have collision chances if you have 256 bit hash which will save you 18 bytes if you choose other text column with 50+ characters avg. This way you can save around 2GB of you indexing space but at the cost of you 4GB of disk space.

Gary Lindahl
  • 5,341
  • 2
  • 19
  • 18