Re: Help! Slow performance

Constantine Kozhukhin <cosk@KandAsoft.com> wrote: >Can somebody help, please? >I have a performance problem with Informix 5.03 on SUN OS 4.1.4. >Queries like the following are running 20-25 seconds on SPARC 4. This is >unacceptably slow for our client production environments. >Table: Colomn: Index: Number of rows: >products prodno prodno 30000 > product product > divno > company > category >division divno divno 11000 > division division > company >Query: >select product, division from products, divisions >where (product matches "*ABC*" or division matches "*ABC*") >and products.divno = divisions.divno >order by product >Making product index a cluster index did not help. No, of course not. The query can't use any indexes except on products.divno and divisions.divno (where I asume you allready have indexes). With * first in the matches this translates to a sequential search. Use "set explain on" and look at the sqexplain.out file to see what your query does. To me it looks like you have a significant database design problem. There seems to be internal meaning in the product and division columns. If this is the case you might want to add extra columns that encodes this meaning, put indexes on them and use them in your query. That will usually speed them up significantly. You shouldn't have to put * first in a matches clause except when searching for porper names (persons, companies, streets and the like). Groupings of products, divisions and similar "things" should allways be done in separate columns. Nils.Myklebust@ccmail.telemax.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company