I should also have noted that you can use MICRORIM_EXPLAIN to show you how R:BASE is optimizing the query, test MANOPT ON and OFF, etc., still paying attention to the number of reads.
I think LIKE will be better than CONTAINS (eliminate the '*' in your example!). How long is the field CustPO? Is it fully indexed? That is, if the length is > 8, have you specified the full field size for indexing? I now defer to Bill. Emmitt Dove Converting Systems Architect Evergreen Packaging, Inc. [email protected] (203) 214-5683 m (203) 643-8022 o (203) 643-8086 f [email protected] -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Doug Hamilton Sent: Thursday, December 09, 2010 13:08 To: RBASE-L Mailing List Subject: [RBASE-L] - LIKE vs CONTAINS on indexed column List, please refresh my memory. I have an indexed column named CustPO. When updating, which will be faster: WHERE CustPO LIKE 'MP000*' or WHERE CustPO CONTAINS 'MP000*' or something else? MP000 is at the beginning of the text in the column. The full clause is: WHERE + T1.CusPnbr = T2.CusPnbr AND + T1.StatCode = 'T' AND + T2.CUSTPO LIKE 'MP0000*' CusPnbr are PK & FK; I assume that should be the first condition in the WHERE clause. StatCode can have one of 5or 6 values, so it is not indexed. Would parenthesis help anywhere? Now that I've thought this through, this might be an improvement, assuming the conditions are evaluated in the order listed, but there were caveats to that also (I think): WHERE + T1.CusPnbr = T2.CusPnbr AND + T2.CUSTPO LIKE 'MP0000*' AND + T1.StatCode = 'T' I know this question comes up every year or so - I apologize for the redundancy. TIA (Bill), Doug
