Re: select single entry and its neighbours using direct-acess to index?

On 2004-12-06, Pierre-Frédéric Caillaud <lists(at)boutiquenumerique(dot)com>
wrote:
> SELECT * FROM test WHERE (name='b' and name2>'a') OR (name>'b') ORDER BY
> name,name2 ASC LIMIT 1;

Write that WHERE clause instead as:

WHERE name>='b' AND (name>'b' OR (name='b' AND name2>'a'))

This is logically equivalent, but it gives the planner a better handle on
how to use an index scan to satisfy the query.

> SELECT * FROM test WHERE (name='b' and name2<'a') OR (name<'b') ORDER BY
> name,name2 DESC LIMIT 1;

That needs to be ORDER BY name DESC, name2 DESC (the direction indicator
applies per-column and not to the output ordering). Same goes for the
WHERE clause in this query as the previous one, too.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services