At 08:26 17.07.2003 +0000, you wrote:

>I just read somewhere else that they say they use the OR to prevent
>the firebird to use the index? Why is it so? Ins't that Index is here
>to improve the performance?

Yes it is, but the optimizer doesn't always know when to stop using
indexes. E.g. I have a table which contains one field which identify a
person and another field that identify a hospital, both of which are
indexed. Occationally I want to retrieve information from two tables,
joined on the person. If my select is limited by another indexed field in
this other table, the ideal plan would contain something like PLAN JOIN
(OTHERTABLE INDEX (OTHERINDEX), MYTABLE INDEX (PERSONINDEX)). However, if I
am only interested in information about this person related to one
particular hospital, the suggested plan may be PLAN JOIN (OTHERTABLE INDEX
(OTHERINDEX), MYTABLE INDEX (PERSONINDEX, HOSPITALINDEX)). This will always
be slower than if it didn't use the hospitalindex, since hospitalindex is
nowhere near as selective as personindex. In general, you can say that I
use OR whenever I see a plan like PLAN JOIN (A INDEX (XA), B
INDEX(<SelectiveIndex>, <LessSelectiveIndex>)) to prevent the optimizer
from using an index on the field that isn't very selective. This can make a
huge difference in speed on tables containing a considerable number of records.

Note that I don't use Firebird 1.5, and that tricks like this may become
obsolete with Arno's improvements (I haven't heard whether it still is
useful or not. Anyone having tried?).

Set