Re: Index help

operationsengineer1(at)yahoo(dot)com wrote:
> before the experts chime in... i read that it is
> fastest to sort your where clause statements with the
> least amount of records returned first.
>
> using this...
>
>
>>WHERE appointments.cal_id in ('82')
>> AND appointments.start_date <= '2005-12-31'
>> AND appointments.start_date >= '2004-01-01'
>> AND appointments.modified >= '2005-01-01';
>
>
> use pgadmin to query your db 4 times - using a
> different where clause statement each time.
>
> put the statement that returns the least amount of
> rows first.
>
> then use pgadmin to query you db 3 times - using a
> different where clause statement (of three remaining)
> each time.
>
> repeat until you have the clauses in order of the
> least records returned.
>
> you still have to think through it, though, since
> records returned can change over time. also, you'll
> want to verify that fewer records actually corresponds
> with less query time.
>
> specific to your case,
>
>
>> AND appointments.modified >= '2005-01-01';
>
>
> would probably return less records than either
>
>
>> AND appointments.start_date <= '2005-12-31'
>> AND appointments.start_date >= '2004-01-01'
>
>
> and should be placed before them in there where clause
> (if it does return less records in the production
> environment).
>
> i can't figure out what
>
>
>>WHERE appointments.cal_id in ('82')
>
>
> does so i can't comment on it.
>
> you also may want to google "sql query optimization."
>
> i hope this information is accurate and i'm interested
> to hear what the real experts have to say on the
> subject.
>
>

Switching around the two (start_date)s may help in practice, Ill try
that out. In testing, the only condition that wont match everything is
the (cal_id).

-A Gilmore