Re: Problem with performance!

In article <01bd537f$4493a280$bb3c2ba0@prn100-02-682e>, Bloomberg L.P <LOGIN_ID@bloomberg.com> writes > > >marko kopac <marko.kopac@mais.si> wrote in article ><35112472.91C1DAE3@mais.si>... >> Hello! >> >> Could anybody help me!!! >> >> How to make my SQL quicker: SELECT * FROM tablex WHERE field1 MATCHES >> '[K,k][O,o][P,p]*' >> >> Index is made by field1, table contains 34500 records, sql working 30 >> seconds. Is that normal? > >This query cannot use an index as shown try this instead: > >SELECT * >FROM tablex >WHERE field1 MATCHES "KOP*" OR > field1 MATCHES "KOp*" OR > field1 MATCHES "Kop*" OR > field1 MATCHES "kop*" OR > field1 MATCHES "KoP*" OR > field1 MATCHES "koP*" OR > field1 MATCHES "kOP*" OR > field1 MATCHES "kOp*" ;> >Art S. Kagel > Or store the same data in uppercase in another field field2 then do select * from tables where field2 MATCHES "KOP*" this is the usual way to do case insensitive searches... PS TO check that field2 is consistent with field1 try putting a trigger on insert and update of the table which riases an exception is the two fields are no consistent. This allows easy application debugging! This trick helped me track down a nasty problem one day this week...turned out a trigger ran a procedure which ran a program which...no wonder I couldn't tell where a row was coming from... -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care