> Firebird can use more than one index per table for a query

In what cases could FB use more than one index on a table? My guess
would be only for OR logic in the JOIN or WHERE clause. Are there any
documents I could read on this, besides the FB Book?

-----Original Message-----
From: Ann W. Harrison [mailto:aharrison@...]
Sent: Thursday, April 14, 2005 4:35 PM
To: firebird-support@yahoogroups.com
Cc: chris@...
Subject: Re: [firebird-support] Query Performance - correlated
subqueries and group by statements


Aaron Abend wrote:

>
> I am using Firebird and have found that correlated subqueries and
> queries involving group by statements are unusually slow (compared to
> MS SQL Server).

If you can send the queries and plans, we may be able to suggest
alternate ways of expressing them that perform better.

>
> I have indexed the tables in what I believe is an appropriate way,
> based on experience in SQL Server and Oracle. Are Firebird index
> strategies significanty different?

Firebird index strategies are slightly different because, unlike most
other databases, Firebird can use more than one index per table for a
query. Thus if you want to use fields A, B, and C to restrict input,
you can index each separately, rather than creating a compound index on
A, B, C. There is also less of a performance penalty in Firebird for
the use of secondary indexes.

However, Firebird indexes do suffer when there are large (> 10,000)
numbers of instances of a single value in an index. Check the list
archives for discussions (many many discussions) of index selectivity.

>
> I also notice that Firebird is not using much memory, which is good, I

> guess, but at a certain point I'd rather let it eat more memory and
> run faster. It's using only 20 mb of memory right now and going fairly

> slow on these subquery and group by statements.

You can add more page buffers (called cache pages in some places) in a
variety of ways - through the configuration file, using a server
setting, or using a tool to set the cache size for a particular
database. The size should not be larger than 10,000 until Firebird 2.

>

Regards,


Ann




Yahoo! Groups Links