kokok_kokok wrote:

> I want to improve the performance of
> select * from foo order by a asc, b desc
>
> If I create 2 indexs, the plan is "natural" and the indexs are not used.

Have you tried creating just index(a) and encouraging FB to use
that index with a dummy where clause or explicit plan?

For example, if you know a is always >= 0 then "WHERE a >= 0"
may have FB select index(a) and, depending on the distribution
of rows, this may turn out faster than natural. (Even if not
ideal.)


Martijn Tonies wrote:

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

It is one thing to explain to a person that Firebird has some
limitations... and to explore alternatives and to check whether
the situation can be avoided.

It is quite another to continually bludgeon that person with
the "fact" that they must be wrong; that anything Firebird does
not do must not be worth doing. (Especially when it obviously
works, even in Firebird, in all but this odd situation.)

We start to seem a little desperate and over protective.
Database and computer science theory is all very useful when it
explains what you see from all products... but when a solution
is available to others but not Firebird then our oh-so-elegant
excuses start to look a little pathetic.

There _are_ situations where you want to (quickly) return just
the first few records of a potentially large set, especially
in user-interactive environments. If Firebird's limitations
prevent it from being optimised to achieve this easily in some
cases (cannot easily create the indexes to make it happen)...
then it is a limitation in Firebird, not (necessarily) an error
in the requirements.

--
Geoff Worboys
Telesis Computing