Wildcard searches

Hi All, We are running Informix 11.5.FC6. In the midst of redesigning a database system to capture new functionality and improve performance. On user request is for searches begining with a '*' wildcard. The fields to search are all varchar(255). I know that regular indexes won't work. I thought the BTS index might, but the documentation says that multiple-character wildcards cannot be used as the first character. What are your suggestions for looking up all records with "*Heart*" and not doing sequential scans? The query will run against at least 5 tables with several million rows. (The example should return "Heart & Lung Assoc", "American Heart Assoc", and "Have a Heart") Feeling a bit too long 'out of the game', Mike Hoffman

The BTS indexes will let you do web-like searches so just "Heart" should find both Heart & Lung Association and American Heart Association. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Aug 12, 2014 at 3:11 PM, MICHAEL HOFFMAN <mrh@panix.com> wrote: > Hi All, > We are running Informix 11.5.FC6. > > In the midst of redesigning a database system to capture new functionality > and > improve performance. > On user request is for searches begining with a '*' wildcard. The fields to > search are all varchar(255). > > I know that regular indexes won't work. I thought the BTS index might, but > the > documentation says that multiple-character wildcards cannot be used as the > first character. > > What are your suggestions for looking up all records with "*Heart*" and not > doing sequential scans? The query will run against at least 5 tables with > several million rows. (The example should return "Heart & Lung Assoc", > "American Heart Assoc", and "Have a Heart") > > Feeling a bit too long 'out of the game', > Mike Hoffman > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0160b9b2c599ff0500744949

Hi Michael, BTS is a full text index based on CLucene. It takes the input text and tokenized the words in the text. The predicates search like term, phase, wildcard, proximity and fuzzy all work on the words in the text that are index and not the text value as a whole. A BTS contains predicate like: bts_contains(col, 'heart') will find any rows with the word 'heart' so it will find the rows your example rows. ("Heart & Lung Assoc", "American Heart Assoc", and "Have a Heart")' Lets say we have documents like: "he has a heart" "the TV show called Heartland was well received" "it was a heartfelt apology" The index would index all the words (excluding the stop words). It would build a term dictionary like: heart tv show called heartland received heartfelt apology and additional structures to associate these words to the input rows along with other information. The index would index all the words in these document. When BTS performed a wildcard search for 'heart*', it looks at the term dictionary in the index and essentially rewrites the query as 'heart or heartland or heartfelt' As you found in the documentation, BTS does not support because CLucene does not support a leading multiple-character wildcard; So it cannot search for '*ed' to find rows containing words: called or received. Hope this helps. -- Mark. Mark Ashworth IBM Informix Extensibility Architect Office phone: +1 (905) 413-5033 Alternate: +1 (905) 697-8094 Email: ashworth@ca.ibm.com Check out my blog From: "MICHAEL HOFFMAN" <mrh@panix.com> To: ids@iiug.org, Date: 08/12/2014 03:12 PM Subject: Wildcard searches [33503] Sent by: ids-bounces@iiug.org Hi All, We are running Informix 11.5.FC6. In the midst of redesigning a database system to capture new functionality and improve performance. On user request is for searches begining with a '*' wildcard. The fields to search are all varchar(255). I know that regular indexes won't work. I thought the BTS index might, but the documentation says that multiple-character wildcards cannot be used as the first character. What are your suggestions for looking up all records with "*Heart*" and not doing sequential scans? The query will run against at least 5 tables with several million rows. (The example should return "Heart & Lung Assoc", "American Heart Assoc", and "Have a Heart") Feeling a bit too long 'out of the game', Mike Hoffman ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Thanks Mark & Art!!! I responded to Art, but via email, asking why the documentation bothered mentioning the first-character multiple wildcard. Mark, you nailed it! That explanation makes a ton of sense! Now to convince my boss to let BTS do the work instead of creating 4 tables to do it. :-)