choosing idx to use

[Anba...@letterkenn-emh1.army.mil]

}From: informix users bb <inf...@hermes1.sps.mot.com>
}Date: Thu, 25 Mar 93 11:24:21 MST
}To: inform...@RMY.EMORY.EDU
}i think this may have been posted previously, but here it goes.
}how does informix sql determine which index to use when executing
}a query? the problem we are having is we have a table that has
}several indexs which include a given column (lets say column_001);
}all but one are composite indexes as follows (as listed by the
}isql - tables - info - indexes ):
}
}create index idx1 on tab1 (columne_001,column_002);
}create index idx2 on tab1 (columne_001,column_009);
}create index idx3 on tab1 (columne_001);
}create index idx4 on tab1 (columne_001,column_005);
}
}when you execute the following query, the idx1 index is used:
}
}select * from tab1 where column_001 >= somevalue;
}
}i know that if i drop the idx1 and idx2 indexes isql will use the
}idx3 index.
} Bob Baskett

When you create a composite index, the first field is also, effectively an
index of its own. This was mentioned in the query optimization article here
yesterday, but perhaps not specifically stated. Actually we have found in
our ancient version of Turbo/SQL/4GL, that putting indexes as you have
slows down the queries; we had to delete the index on _001 alone to speed
up processing. Perhaps it is a quirk in our ancient versions, but it
helped.

Ann Barnes

[Dave Kosenko]

Ann Bar...@letterkenn-emh1.army.mil writes:
|>
|> When you create a composite index, the first field is also, effectively an
|> index of its own. This was mentioned in the query optimization article here
|> yesterday, but perhaps not specifically stated. Actually we have found in
|> our ancient version of Turbo/SQL/4GL, that putting indexes as you have
|> slows down the queries; we had to delete the index on _001 alone to speed
|> up processing. Perhaps it is a quirk in our ancient versions, but it
|> helped.

That is due to the way the "old" (rule-based) optimizer did its job. 4.0
saw the introduction of the Cost-Based optimizer, which takes a completely
different approach to choosing access strategies.

Dave