--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:

>
> Well, excepting that I always prefer to use aliases rather than table names, I wouldn't say there's anything directly wrong with your SQL, Michael. There is, however, one minor error in your email - you don't say which of the queries are quick and which is slow.

I prefer that Firebird uses the RDB$PRIMARY26 index.
using this gives me a result in less than a second.
Not using this causes it to be more than 5 minuttes to do the same!

>
> I'd say the optimizer is left with a tough choice. Either it has to choose decent selectivity for TRANSAKTIONER at the cost of choosing NATURAL for VARER, or it chooses the TRANS_DATO index for TRANSAKTIONER and the optimal index for VARER. Which is better depends on the value of DATO, and the optimizer has no idea whether that part of the query is selective or not (histograms doesn't exist yet in Firebird) - so it guesses.

Didn't see myself, that it actually was two different index used on TRANSAKTIONER.

I would prefer this any day.

PLAN SORT (JOIN (TRANSAKTIONER INDEX (TRANS_DATO), VARER INDEX (RDB$PRIMARY26)))


What seems strange to me is that the same Firebird server on different Databases chooses differently.
Or not - this must have to be with the Selectivity of the indices on TRANSAKTIONER?

>
> If you prefer PLAN SORT (JOIN (VARER NATURAL, TRANSAKTIONER INDEX (TRANS_VAREFRVSTRNR))), then I suspect that
>
> INNER JOIN VARER ON TRANSAKTIONER.VAREFRVSTRNR = VARER.PLU_NR+0
>
> will help the optimizer choose the plan you want. If you prefer PLAN SORT (JOIN (TRANSAKTIONER INDEX (TRANS_DATO), VARER INDEX (RDB$PRIMARY26))), then I'd try
>
> INNER JOIN VARER ON TRANSAKTIONER.VAREFRVSTRNR+0 = VARER.PLU_NR
>

INNER JOIN VARER ON TRANSAKTIONER.VAREFRVSTRNR+0 = VARER.PLU_NR

This actually did the job :)

But - Doing a +0 on a varchar field. What happens if it contains anything but numbers?
My table should only contain numbers, but I can't be sure.



Regards
Michael