Once upon a time there was an FTI contrib module that split up a varchar field
into little bits and placed them into an FTI table to facilitate a full text
index search. It was like being able to do a "SELECT * FROM table WHERE field
LIKE '%value%';" and have it search an index!
It was a great idea! What a pain it was to implement!
You see, a trigger had to be run on INSERT and UPDATE to split up the varchar
field into little pieces. On DELETE you'd have to clear out the rows from the
FTI table. And when you wanted to use the FTI table in a SELECT you had to
write your SQL to join up that FTI table and dig through it.
As I was exploring ways to optimize my application's use of the database, which
has to run the horrid "SELECT * FROM table WHERE field LIKE '%value%';" in
places, I thought this solution could be built upon to allow for an easier
deployment.
AFAICT, the "right" way to do this would be to create an index type which would
take care of splitting the varchar field, and to have the query planner use the
index in a SELECT when a "LIKE '%value%'" is used in the WHERE clause.
Tsearch2 is fantastic, but it works best for fields that contain words. I have
to sift through alphanumeric identification numbers.
Is the split-field FTI the best way to tackle my problem?
What can I do to get better performance on "SELECT * FROM table WHERE field
LIKE '%value%';" ??
CG
__________________________________
Do you Yahoo!?
Yahoo! Mail - Easier than ever with enhanced search. Learn more.
http://info.mail.yahoo.com/mail_250