Search by hashing mysql columns

I have a query which compares values of many columns and returns a the result. Now I have to add another column to compare, whic开发者_如何学JAVAh is a text field and not indexed. Now I am thinking instead of doing this, lets just add a column called hash, which will be the hash of these columns and I can then compare only this against the hash produced by my code for these same columns.

1) I want to know will this result in any performance enhancement and how beneficial is this?

2) Does mysql provide any function to hash combination of columns and store results so that I can hash for already existing columns without having to write separate code for this.

3) Which hash to go for, MD5 or SHA?

4) Is this good idea at all?

I have few million records and few more millions are expected to be generated within months, will hashing these produce unique results for each record?

Thanks, Harsha

---

1) this could result in a performance enhancement although I suspect you reinventing manually what already has been implemented in the indexing engines of the DB... so I would recommend to use built-in index mechanisms...

2) Yes - it has MD5 and SHA / SHA1 SQL functions which hash strings, so you need to convert/cast whatever values you want to hash to a string...

3) That depends on your data - I would guess SHA is a good choice...

4) I don't think so... it is a reinvention of what the DB has already built-in and wouldn't mean that you get rid of indexes... if you want this to perform well you will have to index the column with the hash value...

5) it is not sure to be completely unique but unlikely to produce collisions...

I would rethink the approach and use a composite index. IF a composite index doesn't suite your needs create a column which contains the concatenated values and index that...

---

Try :

MD5(CONCAT(field1,field2,field333);