cwoodall user unknown

mjk Michael Kuhn writes: "[ " "[ "Yet another example of a slooooooow query. "[ " Stuff omitted "[ " 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*" "[ " "[ "-- "[ "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 "[ " One possibility is the index on sp.draw_center_id. Since there are only 92 draw centers you are generating a lot of duplicate index entries for the sp table. I don't know if that is a solution, but if you need an index on it, make it a compound index on draw_center and something else. I think that might improve your performance against sp in normal insert and delete processing as well.You minimize the reorganization of index entries. You might also experiment with the "matches" phrase as well. Can you easily structure a "between" query or a >= and <= on query_last and have your index created ASC. I seem to remember we have had that improve things on occasion, but I think I'd try the index on sp first. Maybe create index sp_idx_1 on table sp(draw_center_id,draw_date) unless the draw_date, if it only includes a date, tends to cause a lot of duplicates. Maybe extend draw_date to a date time, years through minute or year through second. Good luck, -- Con Woodall Colorado St. U.; Veterinary Teaching Hospital; Ft. Collins CO 80523; 303-491-1244 FAX 303-491-4414 cwoodall@vth1.vth.colostate.edu Opinions not nec. employer's, but mine might be right. Then again... --