| Subject | Re: [firebird-support] Find rarely used or unused indices |
|---|---|
| Author | Alexey Kovyazin |
| Post date | 2016-04-08T14:39:12Z |
Hi,
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.
Regards,
Alexey Kovyazin
IBSurgeon
www.ib-aid.com
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.
Regards,
Alexey Kovyazin
IBSurgeon
www.ib-aid.com
Hi
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
jardar