Simplifying the problem, I want the 10 first records, ordered by "a desc, b"

select first 10 * from fo order by a desc, b



My question is: how can I create an index such FB uses it in the above sql statement to get the first 10 records in an efficient way. Natural plan is not a solution, the table has millions of records and it would take too time.

The problem is because "a" is "desc" and "b" is "asc".

If the order is "order by a desc, b desc", or "order by a,b", I can create a specific index and FB uses it for the sort in an efficient way.

In other databases, I can create mixed indexs like (a desc, b), but it seems that in FB is not possible.
In FB, I can create "asc (a,b)" or "desc(a,b)", but I cannot mix them.

--- In firebird-support@yahoogroups.com, "Martijn Tonies" <m.tonies@...> wrote:
>
> Hi,
>
> > I have received 2 reponses:
> >
> > 1) Using the "Natural" plan, but it is not a solution. It is the problem
> > that I have. I cannot use a natural plan in a database with millions of
> > rows.
> >
>
> Well, you're looking at the problem the wrong way OR you didn't explain
> it in enough detail: WHY would you want to potentially transfer millions
> of rows in a resultset to your users?
>
> With regards,
>
> Martijn Tonies
> Upscene Productions
> http://www.upscene.com
>
> Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
> Anywhere, MySQL, InterBase, NexusDB and Firebird!
>
> Database questions? Check the forum:
> http://www.databasedevelopmentforum.com
>