Just download IBPlanalyzer. It will show you the plan your query
uses, as well as what tables are hit, how many records are read in
each table and which of those tables are read through an index.

Basically, the index with the lowest selectivity number (most unique
wins). When joins are involved, just realise that left join forces
the optimiser to use the left table first. Sometimes, there will be
no difference between left join and join, and you can force the
optimiser to choose a better plan. Other times, you will be forcing
the optimiser to choose a worse plan. If one of the conditions in the
where clause can knock out 99% of the records and is indexed, then
that table should probably be in the "from" rather than joined.

If the optimiser is not using an index when it should, it is also
possible that the statistics need to be updated.

But these are all generalisations. They wont always work and it is
not always obvious. I now create my queries from the ground up using
IBPlanalyzer. It takes no more effort to do, and you never end up
with a full table scan. I reduced a query from 3 minutes to about 0.5
seconds just last week, and all I did was switch around the order of
the joins and the switched one of them with the from. It reduced the
workload from 100000 records to about 3 or 4 records.

Adam


--- In firebird-support@yahoogroups.com, developer <developer@g...>
wrote:

>
> Hi,
>
> Is there any available information on how the Firebird database
> works when chooses an access plan to return the result of a query?

Are

> they diferent optimization modes?
>
> I would like to understand the plan analyzer and the

performance

> analyzer to be able to improve my sql queries.
>
> Any help on that?
>
> Thanks in advance.