Re: Online 5.04: Miserable Performace

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 ---