On Mon, Feb 07, 2005 at 09:28:24AM -0800, CG wrote: > 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. <snip> > 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. Seems to me to depends quite a bit or your problem domain. How big are the string's you're searching. If you're not searching on word boundaries like tsearch, you'd need to split on every char. Say you split on three character blocks. So the string "Hello World" would need entries for: "Hel", "ell", "llo", "lo ", "o W", " Wo", "Wor", "orl", "rld" For N character strings you'd need N-2 entries. If you're storing entire documents it's not practical. But if all your strings are maybe 15 characters long (maybe serial numbers), it might be practical. I havn't looked at tsearch but maybe you can customise it to your needs. If you can redefine the split function you could make it work appropriately. Then you can define the ~~ operator (which is LIKE) to call tsearch. This in just off the top of my head, but maybe it can work. Hope this helps, -- Martijn van Oosterhout <kleptog@xxxxxxxxx> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment:
pgp5pmWb3DO50.pgp
Description: PGP signature
