Subject Re: [firebird-support] Index process in Firebird 2.1.4
Author livius
Post date 2017-10-21T10:43:55Z
Hi,
 
It depends on how much the index is selective.
If you have 1 milon of rows and in field A only values 1 and 2 then probably FB will not use this index
but if you have 1,2,3,4,.... then it can be choosed.
 
This really depends and only answer to this question is try as Dimitry suggest.
 
regards,
Karol Bieniaszewski
 
 
Sent: Friday, October 20, 2017 5:45 AM
Subject: [firebird-support] Index process in Firebird 2.1.4
 
 

Hi!
I'm going to develop a commercial program with Firebird2.1, in Delphi7.
I want to know whether the following index is picked by the optimizer.

 


[The first question]
CREATE INDEX IDX1 ON PROJECT(A)
CREATE INDEX IDX2 ON PROJECT(B)
CREATE INDEX IDX3 ON PROJECT(C)
CREATE INDEX IDX4 ON PROJECT(D)

 

SELECT * PROJECT WHERE A=1 and B=2 and C=3 and D=4

The optimizer would be choose the fastest way among table-full-scan and four indexs. Is this right?

 


[The second question]
CREATE INDEX IDX1 ON PROJECT(A)
CREATE INDEX IDX2 ON PROJECT(B,C,D)

SELECT * PROJECT WHERE A=1 and B=2 and C=3 and D=4

The optimizer would be choose the fastest way among table-full-scan and two indexs. Is this right?

 

 

[The third question]
CREATE INDEX IDX ON PROJECT(B,C,D)

SELECT * PROJECT WHERE A=1 and B=2 and C=3 and D=4

The optimizer w ould be choose the fastest way among table-full-scan and IDX index. Is this right?

 

Please tell me.
Thanks!