The query is a basic example, in reality the consult is paged. It sends the partial results from the middle tier to the client tier. The client tier gets the next "page" of records in each user iteration.

I need the results to be indexed, in other case, if the sort is after the read the entire table, the costs is not viable since it can have millions of records. For example, using the index (a,b), the result is in 0.01 seconds, if the index is not used, the result can take 20 seconds or more. In few words, index is absolutely necessary.


It works really fine using the index (a,b), but I would need (a desc, b). This composed index is possible in other SQL platforms, but I cannot see the way in FB.

--- In firebird-support@yahoogroups.com, "Ann W. Harrison" <aharrison@...> wrote:
>
> kokok_kokok wrote:
> > If I
> > If the index is (a,b) then
> >
> > "select * from foo order by a, b" uses the index in the plan, but what I need is:
> >
> > "select * from foo order by a desc, b"
> >
> >
> > How can I create a index to be used in the above sql statement?
> >
>
> You can't. Nor should you want to. Everybody with even a vague
> recollection of a computer science course knows that the cost of
> a sort is nlog(n) while reading from an index is linear with the
> number of records to be read. However the cost of reading records
> from the disk in index order - random relative to disk placement -
> overwhelms the cost of the sort. If you're reading a whole table,
> its much faster to read it in disk order and sort the result rather
> that rattle the disk around finding a record here, a record there.
>
> The exception is a query with a limit/first clause - but even there
> an index on A and a sort on B shouldn't be horribly expensive. If
> it is, then store a pseudo field that's like B, but inverted.
> That's what our descending indexes are.
>
> Good luck,
>
> Ann
>