| Subject | Re: does the order of the conditions in the where clause matters?? |
|---|---|
| Author | Adam |
| Post date | 2006-06-28T04:24:50Z |
--- In firebird-support@yahoogroups.com, Carlos García Trujillo
<cgar1136@...> wrote:
constuct the queries in a way that is easiest for people to read, and
Firebird is clever enough to prefer to use any available indices
first. Only if no index is available could the order of the where
clause potentially affect performance.
One thing you should note is that by default, Firebird stops
processing a where clause for a record if it becomes apparent that it
is not possible for it to evaluate to true. (see Boolean evaluation
method in firebird.conf for more information on this)
known as left outer join) will include the left table even if there
is no matching record in the right table, returning nulls for those
fields. It will only return records from the right table if there is
a corresponding record in the left table. Inner join will not include
the left table unless there is a corresponding record in the right
table, and only include a record from the right table if there is a
corresponding record in the left table.
A common mistake is to use left join for everything, even when it is
impossible to have a record in the left table with no match in the
right table. Firebird will have to consider the left table first in a
left join, where it can choose either table first in an inner join.
Adam
<cgar1136@...> wrote:
>the
> i've been reading about some SQL engines where the order of the
> conditions in the WHERE section of a query is significant...
>
> i wonder if this happen in firebird too??... must i take care of
> order in that my WHERE conditions are made??? i's some test's in adon't
> few databases but the performance seems to be the same... but i
> have really big recordsets in my tables...Not important at all for a well designed database. You should
constuct the queries in a way that is easiest for people to read, and
Firebird is clever enough to prefer to use any available indices
first. Only if no index is available could the order of the where
clause potentially affect performance.
One thing you should note is that by default, Firebird stops
processing a where clause for a record if it becomes apparent that it
is not possible for it to evaluate to true. (see Boolean evaluation
method in firebird.conf for more information on this)
>stuff
> i know that the order of the relations in the FROM Section have a
> significance and so the use of "INNER JOIN" and "LEFT JOIN" as
> like that but i don't know anything about the WHERE section...please
> some guru could tellme if i must start to test all my querys withunder
> different orders in the Where section or this is not important
> the Firebird Platform...Left join and inner join give different results. The left join (also
known as left outer join) will include the left table even if there
is no matching record in the right table, returning nulls for those
fields. It will only return records from the right table if there is
a corresponding record in the left table. Inner join will not include
the left table unless there is a corresponding record in the right
table, and only include a record from the right table if there is a
corresponding record in the left table.
A common mistake is to use left join for everything, even when it is
impossible to have a record in the left table with no match in the
right table. Firebird will have to consider the left table first in a
left join, where it can choose either table first in an inner join.
Adam