Re: Character based searchs in isql 4.1/online 5.0

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. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>