--- In firebird-support@yahoogroups.com, Vlad Orlovsky
<vlad.orlovsky@...> wrote:

>
> Hi All,
>
> I have 2 very basic questions about indexes.
>
> 1) Do I need to create a separate index for a column in a table that

is a foreign key?

No. An ascending index is automatically created on any constraint,
including primary keys, unique constraints and foreign keys so this is
not necessary (unless you require a descending index on the foreign
key field for some other reason).

>
> Ex:
> ROLES table
> ROLE_ID INTEGER -- PK
> ROLE_NAME VARCHAR(50)
>
> USERS table
> USER_ID INTEGER -- PK
> ROLE_ID INTEGER -- FK ?????
> USER_NAME VARCHAR(50)
> ....
>
> 2) Which SELECT is faster(using above tables):
>
> SELECT * FROM USERS WHERE USER_ID = 1 AND ROLE_ID =10
> -- OR
> SELECT * FROM USERS WHERE ROLE_ID =10 AND USER_ID = 1
>

They are both the same speed. The optimiser is smart enough to not
care which way you write it. Although the index is available on both
fields, it will prefer the primary key. Firebird is also able to
combine both indices if required, but probably wont bother in this case.

Adam