"select in" does not use indexes. Use EXISTS() instead.

On 18/02/2015 11:25, martin_gorriti@yahoo.es [firebird-support] wrote:

 

Hi;

I am using FB 2.5.3. in a fast environment (win8 64bit - QuadCore 3.4 Ghz - 8 GB ram).

If I run this query it goes very very fast and returns 84 records:

Preparing query: select distinct "PROC" from "GEST" where ("GEST"."FSAL" between '20080801' and '20080812') order by "PROC"

Prepare time: 0.007s

Field #01: GEST.PROC Alias:PROC Type:STRING(10)

PLAN SORT ((GEST NATURAL))

But if I run this one, it will return 84 records but it needs 10 minutes !!!:

Preparing query: select "PROC"."PROC" from "PROC" where ("PROC"."PROC" in (select distinct "PROC" from "GEST" where ("GEST"."FSAL" between '20080801' and '20080812'))) order by "PROC"

Prepare time: 0.003s

Field #01: PROC.PROC Alias:PROC Type:STRING(10)

PLAN SORT ((GEST NATURAL))

PLAN (PROC ORDER PROC_PK)

Executing...

Done.

-1258819480 fetches, 0 marks, 28351438 reads, 0 writes.

0 inserts, 0 updates, 0 deletes, 11439 index, 962254304 seq.

Delta memory: 25308 bytes.

Total execution time: 0:10:53 (hh:mm:ss)

Script execution finished.

PROC is a table with 11400 rows. Even if the query optimizer takes the worse path (analizing one by one) it seems that 10 minutes es to much ... In my opinion, both may run very quick. Why is the second one taking that long?.

Thank you!