I have a table that stores TEXT information. I need query this table to
find *exact* matches to the TEXT... no regular expressions, no LIKE
queries, etc. The TEXT could be from 1 to 10000+ characters in length,
quite variable. If it matters, the TEXT may contain UNICODE characters...
Example:
CREATE TABLE a (id SERIAL, thetext TEXT);
SELECT id FROM a WHERE thetext='Some other text';
One way I thought to optimize this process would be to store an MD5 hash
of the "thetext" column and query on that:
CREATE TABLE a (id SERIAL, thetext TEXT, thetext_md5 CHAR(32));
SELECT id FROM a WHERE thetext_m5d=md5('Some other text');
Now, obviously I would want to build an INDEX on either "thetext" or
"thetext_md5", depending on which way I decide to make the table.
My question is, what is the absolute fastest way to find the exact match
to a TEXT column? Any amount of pre-processing is fine (such as
calculating the MD5's of all the TEXT tuples), but the query must be
extremely fast.
Has anyone compared (theoretical or practical) the performance of
querying a TEXT-based INDEX versus a CHAR(32)-based INDEX? Is my MD5
idea a waste of time? Is there something better than MD5? Would it be
better to store the "fingerprint" of the TEXT as an integer somehow, so
that the INDEX could be based on a INT* column?
Thanks for any help!
-Jon
--
-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
Jon Lapham <lapham@jandr.org> Rio de Janeiro, Brasil
Personal: http://www.jandr.org/
***-*--*----*-------*------------*--------------------*---------------