Informix SQL select time optimization

Path: emory!wuarchive!zaphod.mps.ohio-state.edu!rpi!uupsi!sunic!chalmers.se!appli!niklas From: niklas@appli.se (Niklas Hallqvist) Newsgroups: comp.databases Keywords: Informix SQL Message-ID: <1356@appli.se> Date: 18 Apr 91 13:55:52 GMT Organization: Applitron Datasystem AB, GOTHENBURG, SWEDEN Hello! I'm using Informix ESQL and have problems understanding how this product does it's queries. Very often when I've typed down a complex select statement I find that it gets real slooooow, in spite of the possibility of getting a fast keyed search. It seems that ISQL's heuristics fails to find the best search path throw the joins. Is there some way to find out these heuristics? If I knew them, I could always rearrange my select statements to reflect the search strategy ISQL imposes on them. The only thing I've discovered myself so far is that the order of the tables specified in the from clause is very important if there are several ways to go about the search. As an example to this problem, I have the following scenario right now: 5 tables A, B, C, D, E The relations between the tables are as follows: for every A-column there are many B:s, both A and B have an index on the column that the join is on. for every C-column there are many B:s, but only C has an index on the joined column. for every D-column there are many C:s, both C and D have an index on the column the join is on, D:s index is unique. (the C index is really a prefix of the composite index mentioned below) for every D-column there are many E:s, both D and E have an index on the column the join is on, D:s index is unique. (the E index is really a prefix of the composite index mentioned below) for every E-column there are many B:s, but only B has a unique index on the joined column. On top of this, there is a unique composite index in C on the columns which joins an C to a combination of B and D. There is a also unique composite index in E on the columns which joins an E to a combination of B and D. Of these tables only B is large (~ 400000 records) and the other ones contain just a couple of thousand records or less. The join between A and B are the one which is important as that's the one which cut down B's size to just some thousand records. What I want to do is this: select lots_of_fields from A, B, C, D, E where A.unique_index_of_A = $user_entered_variable and A.unique_index_of_A = B.unique_index_of_A_and_of_B_too and E.second_member_of_Es_index = B.second_member_of_Es_index and E.first_member_of_Es_index = D.first_unique_index_of_D and D.secod_unique_index_of_D = C.second_unique_index_of_D and C.unique_index_of_C = B.unique_index_of_C; Did you get that??? This query takes hours to complete, it should take minutes. Well actually I solved this using yet another temporary table just containing the joined rows of A, B and C, but thats space-expensive. Niklas -- Niklas Hallqvist Phone: +46-(0)31-40 75 00 Applitron Datasystem Fax: +46-(0)31-83 39 50 Molndalsvagen 95 Email: niklas@appli.se S-412 63 GOTEBORG, Sweden mcsun!sunic!chalmers!appli!niklas