"Duilio Foschi" <dedalus@...> wrote in message
news:3.0.1.32.20020125192030.0068fa14@......

>
> create index MyIndex on MyTable (N_MOV,Y_MOV)
>
> and
>
> create index MyIndex on MyTable (Y_MOV,N_MOV)
>
> have equal results (also in terms of time) on my queries ?

They aren't completely equivalent.
Do you need a query where you filter by Y_MOV alone and another query where
you filter by N_MOV alone? In this case, you better create two indices,
because one of the case is not going to be satisfied by the index. For
example,
index (y_mov, n_mov)
can satisfy
- where y_mov = <something> and n_mov = <something>
- where y_mov = <something>
but it can't satisfy
- where n_mov = <something> so this will be a "natural" (sequential) search.

On the other side, if you need that each tuple be unique, your only solution
is
create UNIQUE index <name> on <tbl>(fieldA, fieldB);

If you go for the compound index solution, I would try to get estimations
about the usage: which field is likely to have more different values? In
other words, which field has less repeated values? I would put that field
first in the compound index. Probably this decision would make the index
itself bigger, but there's no win-win in SW, but always a trade-off.

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing