--- In firebird-support@yahoogroups.com, "starasoris" wrote:

> I have now simplified the indexes to
> Primary Key: fkn_requisitionid, pkn_requisitionitemid
> non unique: fks_stockitemid IDX1
> non unique: fkn_pickslipid IDX2
> non unique: n_unitsordered IDX5
>
> The Plan is still
>
> PLAN SORT (SORT (JOIN (S NATURAL,I INDEX
> (GEN_TABL_REQUISITIONITEM_IDX1,GEN_TABL_REQUISITIONITEM_IDX2,
> INTEG_297,GEN_TABL_REQUISITIONITEM_IDX5))))
>
> Also, just ot be clear. I am not particularly looking for help on
> how to write sql. Although not as knowledgeable as you guys on the
> subject, I have been writing them for years. My main problem is
> that creating an index can cause a 3 second query to freeze
> indefinitly. There are literally hundreds of queries so checking
> and altering any queries that use the same table is not possible.

Actually, I expect you to be as knowledgeable as me in writing SQL - it is just that we all have different strengths that together could improve many databases.

The only general solution that I know for your problem (beyond SQL tweaking), is to be very conservative about how you treat indexes. Don't create a new index (or delete an old) without considering the possible consequences.

Ideally, the optimizer should understand that IDX5 in this case is futile, something it currently obviously doesn't do. I would guess that a (simple) test case that could highlight this problem would be of interest for the future development of Firebird - unless Pavel already has such a case.

Sorry for not being able to be of much help,
Set