Re: Online 5.04: Miserable Performace

If you have large amounts of data you might want to look into the new Informix Universal Server with it's text search datablade (there are several of them I think). That is spesifically designed for this kind of searching and should give you excelent performance as well as significant new options and capabilities. Gabriel Beccar-Varela <beccarvarela@pop.waonline.com> wrote: :Mickey Mestel wrote: :> :> mr. nasty, :> :> > : SELECT count(*) :> > : FROM contacts, companies :> > : WHERE companies.active = -1 :> > : AND contacts.active = -1 :> > : AND companies.company_id = contacts.company_id :> > : AND (description MATCHES "*cancer*lung*" OR :> > : interest MATCHES "*cancer*lung*") :> > :> > Informix is the slowest in a query of char fields. Plus having a * at the beginning :> > of matches statement does not help either. You should turn sqexplain on and see :> > what indexes (if any) this query is hitting. :> :> somone responded earlier that this would do much better as a union. :> check that posting out. :> :> mickm : :I tried using a UNION as suggested and it did not help at all. In fact, :sqexplain.out indicated that with a union the "cost" is about double as :without a union. I came to the conclusion that Informix is utterly :unable to query large CHAR fields. It performs very well with numeric or :short char fields. AS soon as you increase the size of the field beyond :300 chars or so, the performance drops dramatically. : : :-- :Gabriel Beccar-Varela :gabriel.beccar-varela@ucop.edu : : : : : : : : : : : : : : : : : : : : :--- Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org