Hi Sandor!

In general, you're right - it is better to use an index than to not
use it. However, there are exceptions:

- if the index is non-selective
- if you want to return a high porportion of the records
- if a selective index(es) is already used for the table

This means that you normally should avoid using indexes for clauses
like WHERE <PK> > 1 and that using an index for a field named COUNTY
when you're already using an index for TOWN will slow down the select.
Up until now, the optimizer has been good at finding which indexes can
be used, but it is not equally good at restricting itself from using
indexes (it cannot know that a town is always within the same county).

Also, I've found that having a NATURAL or using several indexes on the
first table within a plan rarely kills performance, but do the same to
a table later in the plan and you're in for a lot of trouble.

Generally, I've found that a bit of trial and error as well as trying
to learn a bit whenever Arno answers on this list is a good way to
understand a bit more.

Set

--- In firebird-support@yahoogroups.com, "Sandor Szollosi" wrote:
> I don't understand how these plans and indexes work. Up to now I
> thounght queries work faster if I use indexes.
> But now, after your last answer, I tried a simple query with "ORDER
> BY TBL_1.ID+0" and it gets more faster. I am surprised.
> Can you explain it to me folks, please?
>
> Sandor