--- In Firebird-Architect@yahoogroups.com, Adriano dos Santos Fernandes <adrianosf@...> wrote:

> What's the problem with this approach, as Firebird can use multiple
> indexes in a lookup?
>
> SQL> CREATE INDEX TABLEA_1 ON TABLEA (X1_Y1);
> SQL> CREATE INDEX TABLEA_2 ON TABLEA (X1_Y2);
> SQL> CREATE INDEX TABLEA_3 ON TABLEA (X1_Y3);
> SQL> CREATE INDEX TABLEA_4 ON TABLEA (X1_Y4);
> SQL> CREATE INDEX TABLEA_5 ON TABLEA (X1_Y5);
>
> Select
> ID
> from
> TABLEA
> where
> x1_y1 >= ? - 20 and
> x1_y1 <= ? + 20 and
> x1_y2 >= ? - 20 and
> x1_y2 <= ? + 20 and
> x1_y3 >= ? - 20 and
> x1_y3 <= ? + 20 and
> x1_y4 >= ? - 20 and
> x1_y4 <= ? + 20 and
> x1_y5 >= ? - 20 and
> x1_y5 <= ? + 20;
>
> PLAN (TABLEA INDEX (TABLEA_5, TABLEA_4, TABLEA_3, TABLEA_2, TABLEA_1))
>

yes, but there is room to improve performance
in this particular case for example, if you add an index
on (X1_Y1, X1_Y2, X1_Y3)
and rewrite the query like this :

where
x1_y1 in (...)
x1_y2 >= ? - 20 and
x1_y2 <= ? + 20 and
x1_y3 >= ? - 20 and
x1_y3 <= ? + 20 and
x1_y4 >= ? - 20 and
x1_y4 <= ? + 20 and
x1_y5 >= ? - 20 and
x1_y5 <= ? + 20;

it perform better
but we should certainly have even better performance with indexes like R*tree (Postgresql use Gist, but I did not tested it yet with a case like that, MssQL use kind of grid http://technet.microsoft.com/en-us/library/bb964712.aspx)