Re: Character based searchs in isql 4.1/online 5.0

In <25ftprINNj3r@emory.mathcs.emory.edu> rent2.informix.com!johnl@emory.mathcs.emory.edu (Jonathan Leffler) writes: >In comp.databases.informix you write: >>We have a performance problem related to searching a character based >>credit card number. We have a table with 153,000 credit card entries, >>each card field has 14 characters. We need to do a look-up search on >>specific card number as quickly as possible. Current searches use the >>following example of select in 4GL or isql: >>SELECT customer_id >>FROM card_table >>WHERE card_number>>MATCHES "12345678901234" >>This search is taking about 1 min, 20 sec. Much too long! What do we do? >>Change the field type to a real number? We don't think an index on the >>field will help much, but we will try. Is there different SQL approach to >>speed this up? >Use equality instead of MATCHES, and make sure there is an index on the >column. Only use MATCHES when you must (emphaises MUST) use wild card >searching. You don't have to use wild card searching for fixed prefix >searches: > x MATCHES "abc*" > x[1,3] = "abc" >both return the same data, but the equality operator will use the index on >the column, whereas MATCHES (probably) won't. John's not usually wrong, but I thought MATCHES with a leading constant should use and does use an index if present. Unfortunately, I don't have time to test my theory before going on vacation tonight, as my usual server is down (due to flood, not Pyramid reliability), so I'll try to remember to confirm my belief when I get back. graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.