On Aug 19, 2011, at 10:45 AM, "reynaldi81" <reynaldi81@...> wrote:

> @Ann Harrison: Thanks for the detail explanation. so i guess i have the indexes correct.

I am not convinced. firebird works better, generally, with single column indexes that it can combine and queries that are selective enough that the results can be sorted in memory.

>
> @svein_erling: actually i'm trying to retrieve only one index. I forgot to put "first 1" on my first post.

Assuming you mean "one row", that is a significant factor. Do you have an estimate of the total number of rows your query would select?

>
> I have done some more checking and found out that because i use join, the query wont use the descending index.

Right. I forget all the rules about navigational access, but it certainly will not override join order optimization, so if the join order says take the other table first, you will not get navigational access. There are probably other rules that affect joins as well.

> I did more testing and try these queries
>
> "select col1 from table1 where col2=1 and col3=1 and col4=1 and col5=1"
> and
> "select col1 from table1 where col2>1 and col3>1 and col4=1 and col5=1"
>
> index1 (col2, col3, col4, col5)
> index2 (col4, col5)
>
> query no 1 uses index no 1
> and query no 2 uses index no 2.
>
> Can you guys tell me what happen?

Yes. Firebird can use a field in a compound index only if all the previous fields are compared for equality. Think of an index as a list sorted by the key values. Firebird can only use an index to find a contiguous sublist. If your list had values like 1,1,1,1 1,1,2,1 2,1,1,1. THe first and thir qualify. The second does not.

you would get better results with an index on each column.

Good luck,

Ann

>
>
>
>

[Non-text portions of this message have been removed]