Subject Re: [firebird-support] Foreign Key & Query Performance
Author Mark Rotteveel
On 24-2-2018 11:40, antoedincharley@... [firebird-support] wrote:
> I am having a doubt that if Foreign Key reduces Query Performance Or not.
> My Environment is
> FirebirdCS2.6 :-
> Item Table :- 10 Records, PK - Itemcode Varchar(5);
> BillDetail Table :- 10 Million Records (FK Selectivity - 0.0076, PK
> Selectivity - 0.000000076)
> I am having FK between BillDetail.Itemcode And Item.Itemcode with FK
> every query is slow. but as i remove the FK and added Index with
> Itemcode&BillDetail_PK the queries are running lightning fast. But I
> prefer FK to be in Database with high performance queries.

It could mean the statistics on the index of the primary key or foreign
key are not up-to-date, so Firebird doesn't take the index into
consideration. Try updating the index statistics, see also

If it still doesn't work, we need more information, like the full DDL,
the query and the execution plan in both situations.

Mark Rotteveel