Subject Re: [ib-support] Utility to find slow queries?
Author Svein Erling Tysvaer
At 17:46 18.02.2003 +1000, you wrote:
>That comes with Interbase, IIRC. I'll have to get the D6 disk out to do
>that. Groan.

Don't bother. I simply use IB_SQL (freeware from, but
there does exist a utility called IB-Planalyzer or something that I've
heard is quite good (though I must admit I don't know how well it works
with Firebird).

>On of the queries uses an index with an uneven distribute: status of a
>piece of
>work throught the factory. By far the greatest number of them are in ARCHIVED
>(4) status. So the query has to ask WHERE STATUS <= 3, which I imagine
>efficently reduces the number of records to a fairly comstant number, with
>efficiency not affected by the large number of records that fail that
>But there is also another condition.

This index may cause things to slow down when doing updates and deletes due
to its very low selectivity. Whenever you index such a field, make sure you
index it using (<low selectivity field>, <pk>). Since archived status is
the highest number, your index will work well if it is ASCending (which is
the default).

Too many indexes may be as bad as too few indexes. But as Helen said, we
must see the SQL, the generated plan, index definitions and preferably a
bit information about your database (e.g. number of records in table and
number of records with status <=3). Until then, we can only give you
general advice - which may or may not be appropriate.