> Simplifying the problem, I want the 10 first records, ordered by "a desc,
> b"
>
> select first 10 * from fo order by a desc, b

You have mentioned that several times, to which my reply was:

Why do you want the first 10 (or second 10 or whatever) rows of MILLIONS
of rows? Because you want to "page" them? What user wants to go to page
237232 of this list?

>
> 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.

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

>
>
>
>
>
>
>
> --- 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
>>
>
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>