Re: Character based searchs in isql 4.1/online 5.0

->Date: Fri, 20 Aug 1993 18:59:27 -0600 ->To: informix-list@rmy.emory.edu ->From: hanan@salsa.abq.bdm.com ->Subject: Character based searchs in isql 4.1/online 5.0 ->Cc: wright@salsa.abq.bdm.com -> ->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 to 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? -> ->Our environment is: OnLine 5.0.0.uc2, isql 4.10.uc2, esql/c 4.10.ud1, ->486/33PC, 16MB RAM, Interactive Unix S5R3.2,v3.0.1. -> ->please respond to hanan@abq.bdm.com or wright@abq.bdm.com -> ->Thanks in advance. -> ->Jeffrey D. Hanan ->hanan@abq.bdm.com ->Phone:(505) 848-5362 ->Fax: (505) 848-5720 DEFINITELY try an index on card_table.card_number!! And it seems to me it should be a UNIQUE index. I have used this simple technique to reduce access times from multi-minutes to sub-second on several occasions. One of the best features is that your application can be completely unchanged. The engine will notice the new index and use it. However, while you don't need to change the app to use the index, Bob Baskett's suggestion to consider using '=' instead of 'MATCHES' for this search is a good one. Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, Tech Ops | / \\ alan@den.mmc.com | P.O. Box 179, M/S 5422 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\