4

MySQL natively doesn't support hash indexes. So, making a pseudo hash column and creating an index on a hash column needs some thinking.

And it seems to be widely understood that if the text field is long then hashes are worth the overhead they require. But how long should text be to start thinking about hash?

If I intend to use 128bit hash then how many characters threshold would be a sufficient minimum for a text column?

Edit Details listed in my another question maybe useful https://stackoverflow.com/questions/7403167/index-on-url-or-hashing-considering-ram

| improve this question | |
  • What is your requirement? An index only comes into play when there will be queries in a form that the query optimizer will use them. What is the nature of the data in question? What type of queries will you be doing that involve this data? – gview Sep 14 '11 at 18:18
  • Here is the description of the project stackoverflow.com/questions/7403167/… – Rick James Sep 14 '11 at 18:49
3

Its starts to become reasonable when it starts to save your disk. But at the same time it starts to take your computational power.

Nowadays disk space is considered cheap but if you are using SSD then its not that cheap. As you need exact match then it will not take too much CPU so it may be a better option.

And exact answer of your question about the length of text column to satisfy the use of hashes can vary based on lot of factors starting from DBA's skill to load on system.

But if you save more than 50% of RAM using hashes and total RAM saving is at least 2GB then I think it maybe worth to take this route otherwise it maybe an unnecessary rehearsal.

| improve this answer | |
3

I'm not sure what are your reasons for wanting a hash index.

But if you are looking for indexing text/varchar columns then you can go with MyISAM/full-text or you can use something external like Sphinx or Lucene.

You can alternatively, create an additional column with a hashed version of another and as long as its 1000 bytes or less, then it can be indexed by MySQL.

I hope I have answered your question.

| improve this answer | |
  • My reason for looking into pseudo hash index is because my table contains 150+ million records and I have limited memory (8GB) so I want to optimize my indexes and don't want to put index on text column because it will eat up too much RAM. – Rick James Sep 14 '11 at 17:55
  • You can (and have to) specify how much of a text column's characters you would like to index. As in mytextcolumn(1000) - 1000 being the limit. I would let MySQL handle the memory part as it is very good on that side. Alternatively, using Sphinx for these things would probably be the most suitable tool for the job. – Jonathan Sep 15 '11 at 6:58
  • Why should I put index on text column when it has variable length instead of creating a hash column and putting unique constraint on hash column. Only problem in putting index on hash column is minimizing the collision as low as possible. – Rick James Sep 15 '11 at 16:42
  • You can create another hashed column and index it as well as add your own code to your application to search it that way. I would just say that this is a problem that has already been solved using other tools and that has largely been verified by other people. If you would like to see if your way works and is less complex, then my all means, give it a try. – Jonathan Sep 16 '11 at 10:41
  • Actually those tools are for full text search solution like sphinx. But I am more interested here to know when it becomes to be feasible to take 16 bytes of hashes. Like, instead of storing 100 bytes of text column (when added index) use of 16 bytes is worthy. And most importantly only exact match is required not range scans. – Rick James Sep 16 '11 at 18:45
0

Having read your description, I could see how a hash would make sense. However, when you talk about memory use the size of an index column does not make it impossible for searching of a large data set. The basic index technology of mysql is a btree. Btrees divide and conquer.

The specific advice I can give is this: let's assume you're going to use an md5() hash. For maximum efficiency you should make the hash the primary key of innodb engine tables where the hash is a char(32). Since innodb has clustered indexes, retrieving the data will save on disk reads, and there is also reduced work that needs to be done when you use a char, as you will be able to do since the result of the md5() hash never changes.

| improve this answer | |
  • When the purpose is to save RAM then using InnoDB would be a wise decision? – Rick James Sep 15 '11 at 16:44

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.