| Subject | Re: [firebird-support] Problem with sub-optimal query plan |
|---|---|
| Author | Tim Ward |
| Post date | 2015-02-03T17:12:01Z |
On 03/02/2015 17:05, Fulvio Senore mailing@... [firebird-support]
wrote:
inner join goes orders of magnitude faster (again, as in this case, you
have to convince yourself first that you're going to get the same results).
In an ideal world, changing the spelling of a query without changing its
meaning would not have any effect on the query plan generated, but we
are where we are, and it's sometimes useful to be able to prod it in a
different direction. Such tricks are not, of course, going to be
portable between database engines.
--
Tim Ward
wrote:
> Thank you, using a left outer join did the trick!Sometimes it's the other way around - replacing an outer join with an
> Now the query uses the descending index and it is much faster.
>
> I agree that it is not an optimal solution but for this database an
> outer join will return the same rows as an inner join (no nulls) so it
> solves my problem.
inner join goes orders of magnitude faster (again, as in this case, you
have to convince yourself first that you're going to get the same results).
In an ideal world, changing the spelling of a query without changing its
meaning would not have any effect on the query plan generated, but we
are where we are, and it's sometimes useful to be able to prod it in a
different direction. Such tricks are not, of course, going to be
portable between database engines.
--
Tim Ward