| Subject | [firebird-support] Re: 'order by' and 'union' v.s. 'union all' have impact on the number of indxd reads |
|---|---|
| Author | Svein Erling Tysvær |
| Post date | 2008-03-17T08:18:22Z |
You've forgotten something important, to tell us which version of Firebird you are referring to, show us the select statements, the plans generated and tell us how many records are returned (and someone might be interested in your number of reads as well, I just don't know enough about them to be very interested myself). Hence, I'll only guess a partial answer...
In Firebird 1.5 lets say I do
SELECT MyPK
FROM MyTable
WHERE IndexedField = 1
That will give a plan similar to
PLAN (MyTable INDEX(IndexedField))
If I change it to
SELECT MyPK
FROM MyTable
WHERE IndexedField = 1
ORDER BY MyPK
I may see the plan change to
PLAN (MyTable ORDER MyPK)
This doesn't mean that Firebird doesn't use the index for IndexedField, it is just on a 'lower' level that isn't surfaced in the plan.
Using the index for ORDER BY may or may not be beneficial. Try and see for yourself whether it is useful in your case. You can prevent it from being used by doing something like:
SELECT MyPK+0 as MyPK
FROM MyTable
WHERE IndexedField = 1
ORDER BY 1
From Firebird 2 (I think, it might be 2.1), both levels of the PLAN are surfaced and you can see both the index used for selecting and ordering.
HTH,
Set
In Firebird 1.5 lets say I do
SELECT MyPK
FROM MyTable
WHERE IndexedField = 1
That will give a plan similar to
PLAN (MyTable INDEX(IndexedField))
If I change it to
SELECT MyPK
FROM MyTable
WHERE IndexedField = 1
ORDER BY MyPK
I may see the plan change to
PLAN (MyTable ORDER MyPK)
This doesn't mean that Firebird doesn't use the index for IndexedField, it is just on a 'lower' level that isn't surfaced in the plan.
Using the index for ORDER BY may or may not be beneficial. Try and see for yourself whether it is useful in your case. You can prevent it from being used by doing something like:
SELECT MyPK+0 as MyPK
FROM MyTable
WHERE IndexedField = 1
ORDER BY 1
From Firebird 2 (I think, it might be 2.1), both levels of the PLAN are surfaced and you can see both the index used for selecting and ordering.
HTH,
Set
--- In firebird-support@yahoogroups.com, "vincent_kwinsey" wrote:
>
> Hello!
>
> I am trying to find optimal select statement. Currently I have only
> indexed reads (no non-indexed reads), but I am still trying to
minimize
> this number - on some test cases I can see improvement some 10
times -
> for the number of reads and execution time as well.
>
> There are strange things:
> - adding 'order by 1, 2, 3' clause removes all the optimization and
> there is max number of reads
> - 'union' remove all the optimization as opposite to 'union all'.
> In case of 'union' the total number of reads exceeds the sum of reads
> for individual selects (which are parts or 'union').
>
> Intuition says than in both situations there should be some fixed
> amount of reads and the 'order' or 'union' should simply make some
> sorting or elimination of duplications of some temporary set and
> there should be no additional reads...