|Subject||Re: [firebird-support] Query optimization|
|Author||Svein Erling Tysvaer|
>Did you notice that neither of the criteria in the WHERE clause is involvedYes.
>in the joins?
>Did you notice the low selectivity of the index you suggested?Nope, I assumed CODE was fairly selective for both CLIENT and CLNT_SUB.
Isn't it the case?
Ahh, and no, I didn't notice that POS_ID had only 4 possible values. Then
we should ask him to drop all such indexes (which you already did) and ask
if SHIFT_NUM is more selective and a potential candidate for indexing.
>You did notice the fact that there were composite indexes stepping on oneadded SUB_CODE as well, but didn't notice.
>another but you didn't notice the low selectivity of all of them.
>You didn't make any recommendations about indexing for the joins as far as
>I can tell. But maybe you posted something that I missed...
>Assuming CODE is fairly selective, the following two indexes should help you:
>CREATE INDEX IDX_CLNT_SUB_CODE ON CLNT_SUB(CODE); //Here I should have
>CREATE INDEX IDX_CLIENT ON CLIENT(CODE);Set