| Subject | SV: [firebird-support] index use when using order by |
|---|---|
| Author | Mikkel Andersen |
| Post date | 2019-09-23T21:17:41Z |
Hi.
I couldn’t agree more, and for two reasons:
- Is it obvious to all that this is required to avoid a severe performance penalty? It wasn’t to me, thats for sure.
- Adding indices to tables (especially those heavy on writing records), is also a performance concern. So it’s a bit like choosing between two evils.
Best regards
Mikkel Andersen
Fra: firebird-support@yahoogroups.com <firebird-support@yahoogroups.com>
Sendt: 18. september 2019 08:04
Til: firebird-support@yahoogroups.com
Emne: Re: [firebird-support] index use when using order by
On 2019-09-18 06:46, liviuslivius liviuslivius@...
[firebird-support] wrote:
> HiThis is in my opinion an annoying limitation of Firebird. The indexes
>
> your problem is that you have ascending index, which is not usable in
> descending queries. You need to create descending index and it then
> can be used in e.g. max queries. Ascending index is used in min
> queries and order by xx asc.
are theoretically bidirectional, but aren't used that way because of how
index pages are written. The write strategy makes it possible for a
reverse read to miss index pages when the index is modified (page split)
while it is read. I think there must be a strategy that can be used to
address that.
In other database an 'ascending' index can also be used for descending
reads, there ascending and descending are applied to individual columns
to create different key orderings in the index.
Mark