>>> On Fri, Oct 12, 2007 at 9:57 AM, in message
<1192201021(dot)6170(dot)47(dot)camel(at)localhost(dot)localdomain>, Theo Kramer
<theo(at)flame(dot)co(dot)za> wrote:
>
> select * from foo where
> (a = a1 and b = b1 and c >= c1) or
> (a = a1 and b < b1) or
> (a > a1)
> order by a, b desc, c;
>
> I have, however, found that transforming the above into a union based
> query performs substantially better.
Another approach which often performs better is to rearrange the logic
so that the high-order predicate is AND instead of OR:
select * from foo where
( a >= a1
and ( a > a1
or ( b <= b1
and ( b < b1
or ( c >= c1 )))))
order by a, b desc, c;
With the right index and a limit on rows, this can do particularly well.
-Kevin