Обсуждение: Curiousity about indexing, again

I recently asked why my "select * from hltdata order by stmp limit 10"
did not use the index; Hiroshi Inoue, Inoue@tpf.co.jp, told me that
LIMIT is ignored by the optimizer.  Apparently it assumes it will
return every record, so a sequential scan is good enough.  Adding
"where 'now' > stmp" (stmp is type datetime) tricks the optimizer
appropriately.  However, it only does so for "order by stmp".  If I
use "order by stmp DESC", explain says it will have to sort before
doing the index scan.  Is this reasonable behavior?  And is there some
way to create an index that it will use for "order desc"?  I tried
adding DESC after the column name when creating an index, but no luck.

--
            ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
     Felix Finch: scarecrow repairman & rocket surgeon / felix@crowfix.com
  PGP = 91 B3 94 7C E9 E8 76 2D   E1 63 51 AA A0 48 89 2F  ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o

At 07:11 +0300 on 05/07/1999, Felix Morley Finch wrote:


> Adding "where 'now' > stmp" (stmp is type datetime) tricks the optimizer
> appropriately.  However, it only does so for "order by stmp".  If I
> use "order by stmp DESC", explain says it will have to sort before
> doing the index scan.

Hmm. It strikes me that if "where 'now' > stmp" helped you with an
ascending sort, something like "where '-infinity' < stmp" should help you
with a descending sort. But this is merely a shot in the dark.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma