Subject Re: [firebird-support] Find rarely used or unused indices
Author Alexey Kovyazin

Check your database with Database Analyst from HQbird (trial will be enough) and look at the list of Useless indices (indices with only 1 key for all values) and Bad indices (with a few keys for many values).

Then, you need to run tracing of your database in action, for example, with HQBird PerfMon with option Show plan text, and analyse log database for indices included in plans - essentially, build a list of them (it should be done manually).

Then perform
select rdb$index_name from rdb$indices
and compare these 2 lists, then figure out which indices are not used.

In general, this idea is not so good as it appears - if index has good selectivity, Firebird usually uses it very effectively, until you have some abandoned parts in the database, where you never send SQL queries.

Alexey Kovyazin


What is the best way to go about to find out about this?

I have several projects that are using the FB database and I would like to cleanup and remove all the indices that are just taking up space and thus indirectly reduces performance?

Best regards