Query Optimizer or Lack Thereof

Yet another example of a slooooooow query. This does get frustrating because there are too many times that a perfectly good query has to be split into parts (ie. temp tables) in order to get some decent access time. In the query described below, Does anybody have a clue as to why a sequential search occurs on draw_center? Possibly Informix techs What rules does the query optimizer use? Does anybody know? There are only 92 rows in draw_center. But there could be many deleted rows. A sequential search will go through the entire file. a lot of rows - in person a lot of rows - in sample a lot of rows - in sample_package 92 - in draw_center indexes on p.id p.query_last s.person_id s.sample_package_id sp.draw_center_id dc.id QUERY: ------ select p.query_last, sp.id, sp.date_drawn, dc.center_cd from person p, sample s, sample_package sp, draw_center dc where p.id = s.person_id and s.sample_package_id = sp.id and sp.draw_center_id = dc.id and query_last matches "SMITH*" Estimated Cost: 9962 Estimated # of Rows Returned: 1621 1) kuhn.dc: SEQUENTIAL SCAN 2) kuhn.sp: INDEX PATH (1) Index Keys: draw_center_id Lower Index Filter: kuhn.sp.draw_center_id = kuhn.dc.id 3) kuhn.s: INDEX PATH (1) Index Keys: sample_package_id Lower Index Filter: kuhn.s.sample_package_id = kuhn.sp.id 4) kuhn.p: INDEX PATH Filters: kuhn.p.query_last MATCHES 'SMITH*' (1) Index Keys: id Lower Index Filter: kuhn.p.id = kuhn.s.person_id After an hour or so of fooling with this query.... Adding this correlated subquery to the end causes the matches search to take place first. Query is now instanteous. and p.id in (select person_id from sample where person_id = p.id) select p.query_last, sp.id, sp.date_drawn, dc.center_cd from person p, sample s, sample_package sp, draw_center dc where p.id = s.person_id and s.sample_package_id = sp.id and sp.draw_center_id = dc.id and query_last matches "SMITH*" and p.id in (select person_id from sample where person_id = p.id) b -- Michael J. Kuhn Consultant phone:410-254-7060 Email: rhlab!kuhn@uunet.uu.net or uunet!rhlab!kuhn c/o Baltimore Rh Typing Laboratory, Inc. phone:410-225-9595