Hi;
I think there is not a problem in my query, but there is a problem in the
query optimiezar;
Under 2.5.0 the query optimizer builds a plan using MOVI_FECH:
PLAN SORT ((MOVI INDEX (MOVI_PROC, MOVI_FECH)))
But under 2.5.8 the query optimizer does not use MOVI_FECH
PLAN SORT ((MOVI INDEX (MOVI_PROC)))
But, if with 2.5.8 I only execute the MOVI filter:
select distinct "PROC" from "MOVI" where "MOVI"."TIPO" in ('1','A','B') and
("MOVI"."FECH" between '20190301' and '20190412') and "MOVI"."MIEM" =
'15JMS45D7A' and "MOVI"."HECH" = 'N' order by "PROC"
... then I get a super-fast answer and the optimizer uses MOVI_FECH
PLAN SORT ((MOVI INDEX (MOVI_FECH, MOVI_MIEM)))
This is not a little diference. If the engine have to analize 1.1 millon
records one by one, and more over when MOVI is a very big table with blobs (I
don't know if the engine is fetching blobs for this case but if it does ... you
know).
So the IN is not a problem. The problem is about why the enigine is not using
MOVI_FECH index when it is there and is very usefull for the case.
Thank you.
