Fast Text LIKE indexed searches -- but how?
Hi, We're porting from Illustra but we've noticed that LIKE searches on VARCHAR(100) fields isn't very fast. This is after standard indexing. Are there any options that can be set upon creation of the index or perhaps a datablade which can do this? It'd be nice if the datablade didn't go overboard supporting PDF/HTML/Word documents held in tables -- just a simple, fast LIKE style search. BFN, John. -- John Wright @ whereonearth.com limited
John Wright wrote: > Hi, > > We're porting from Illustra but we've noticed that LIKE searches on > VARCHAR(100) fields isn't very fast. This is after standard indexing. > > Are there any options that can be set upon creation of the index or perhaps > a datablade which can do this? It'd be nice if the datablade didn't go > overboard supporting PDF/HTML/Word documents held in tables -- just a > simple, fast LIKE style search. More info on this LIKE search we're doing. Nearly 50000 rows returning an indexed numeric id. The LIKE is done in a case sensitive way not an upper(column_name) like upper('%match%'). It takes about 6 seconds to return 6 rows for a column_name like '%match%' search which isn't very good at all -- the hardware is certainly up to it. BFN, John. -- John Wright @ whereonearth.com limited
john@REMOVE.infernet.com (John Wright) writes: > John Wright wrote: > > Hi, > > > > We're porting from Illustra but we've noticed that LIKE searches on > > VARCHAR(100) fields isn't very fast. This is after standard indexing. > > > > Are there any options that can be set upon creation of the index or perhaps > > a datablade which can do this? It'd be nice if the datablade didn't go > > overboard supporting PDF/HTML/Word documents held in tables -- just a > > simple, fast LIKE style search. Excalibur Text Datablade? > More info on this LIKE search we're doing. > > Nearly 50000 rows returning an indexed numeric id. The LIKE is done in a > case sensitive way not an upper(column_name) like upper('%match%'). LIKE '%SsEeAaRrCcHh%' is probably both better and slower:) > It takes about 6 seconds to return 6 rows for a column_name like '%match%' > search which isn't very good at all -- the hardware is certainly up to it. Seems slow -is the index really used? I'd definitly take a close look at Excalibur. Thomas