On Thu, 10 Mar 2016 21:16:28 +0200
R Smith <rsmith at rsweb.co.za> wrote:
> > Hmm, does this work any better?
> >
> > SELECT id FROM t
> > ORDER BY id < 'pen' desc, id;
>
> It works, but not better. I think it was Igor who proposed similar
> (if not, apologies) which of course produces the correct result, but
> cannot take advantage of the index on id so it becomes a result-set
> walk causing longer ordering of values - exactly what the OP tried to
> avoid.
Hmm, I don't know about "cannot", but I'm not surprised by "does not",
because it's a tough inference.
If you stand back a minute, you can see that
id < 'pen'
is a monotonic function of "id" if "id" is sorted. The query processor
*could* include that logic, and could choose to process the rows, in
index order, starting with the first row where id >= 'pen' , to the
end, and wrapping back to the beginning.
The big boys do that kind of thing. In general "order by f(x)" will
use an index on x if f(x) has the same order. For hard problems, they
support computed columns -- a little like a view attached to a table --
and indexes on them.
In SQLite, I guess the OP's only solution is to make an index of the
kind he needs.
create table idx as select id < 'pen' as 'LT_pen', id from t;
select id from idx order by LT_pen;
Of course, that presupposes 'pen' is a constant. Whether or not that's
true wasn't mentioned in the original post.
--jkl
