Re: Query Optimization ... Is this all I can get??

Yes - (amendment to my own response) - it's usually best to head an index with the most selective column, unless you have lots of queries that only reference the other column(s) or have an ORDER BY or GROUP BY that requires the index to be in a particular sequence. As for UPDATE STATISTICS, I'd have thought it would be so blindingly obvious to the optimiser to choose your new index that LOW should suffice. Andy Frank Langelage <frank@lafr.de> wrote in message news:<bs92od$ap01i$1@ID-48907.news.uni-berlin.de>... > Anthony Presley wrote: > > > > create table phone ( > > id serial primary key, > > areacode integer, > > phone varchar(20), > > phone_id integer, > > type_id integer, > > type varchar(20), > > phone_id references phoneDef > > ) lock mode row;> > > > phone_id is an external reference to a table containing phone number > > definitions (ie, Primary Phone, Cell Phone, etc...) and type is > > something like "Office", "User", "Company" and type_id is manually > > updated by the software to ensure that it is the id of the user / > > company / office. It has about 300K rows, but each user only has (at > > most) 3 phone numbers. > > > > When I want to do a: > > SELECT areacode, phone FROM phone WHERE type = 'User' and type_id => > ? > > > > It takes a little too long for my liking. About 1300 milliseconds. > > This is fine for ONE, but when I need to fetch 1500 or more, using > > another > > looping query, I end up with .... it taking 42 minutes. > > > > IE, I do: > > > > FOREACH user [42 minutes] > > FETCH addresses (if exist) [About 400 milliseconds] > > FETCH phones (if exist) [About 1300 milliseconds] > > END FOREACH > > > > At about 1700 milliseconds per user, that takes some time. My SQL > > Explain is saying: > > > > QUERY: > > ------ > > select areacode, phone > > from> > phone where > > type = 'User' and type_id = '75543' > > > > Estimated Cost: 7571 > > Estimated # of Rows Returned: 2 > > > > 1) root.phone: INDEX PATH > > > > Filters: root.phone.type_id = 75543 > > > > (1) Index Keys: type > > Lower Index Filter: root.phone.type = 'User' > > > > How in the heck does one speed this up? An OUTER join MAY be feasible, > > but takes the cost from 7571 to over 46,000. The FOREACH query has a > > cost of 43. > > > > Not really sure how to speed this one up .... seems it doesn't get > > much more simplistic than following an INDEX PATH. What am I missing? > > Figure there must be some Informix query to help this along. The > > table is under 10MB in size. > > > > Any ideas? I just updated the statistics [low]. > > > > I would create an index with fields type_id and type in this order. > Field type has very few values, but type_id will have many different values. > Then update statistics again according to the manuals (dostats: table > phone (MEDIUM), column id (HIGH), column phone_id(HIGH)) and let us know > the performance increase. > > Regards > Frank