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 ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
