Re: How should I make text column search faster?

[Mirabell Stoffen]

Rick, you are always very helpful.

As there will no search for parts of the address like Street, City etc so exact matching of hash is fine. And i know there will be no range scans and its not needed too (though its not possible with hashes too).

One more clarification about search type is that it would be exact match.

>>Since either the hash or the direct index will be too big to be cached, why not >>keep it simple and not do the hash.

Well, hash would take only 16 bytes in binary(16) but index on address column would take 70bytes so don't you think its a good difference worthy to bear the overhead of calculating hashes?

Currently its MyISAM and I intend to keep it because though there are lot of benefits of InnoDB but as it uses clustered index so it will be heavy on RAM.

After index is built on hash column.. do you think there will still be need for declaring unique constrain on address column? If suppose address column is declared unique then there will be two indexes in total? And all the benefits of doing hashes will be gone?