Subject Re: [firebird-support] Re: Can't index a 1k+ utf-8 column
Author Ann W. Harrison
woodsmailbox wrote:
> Is there any plans to remove the limitation that makes the max. index
> size dependent on database page size (i.e. have an index node span
> multiple pages) ?

I can't speak for the project, but as a developer of database with
a few decades of experience, no, b-trees don't work with keys that
are larger than ~ a quarter of the page size. They get terribly
deep and indexes accesses end up slower than tables scans.

If you're doing equality lookups, I'd suggest maintaining a hash
of the field with triggers and indexing the hash. The firebird
developers may decide to include hash indexes, in some version,
but those indexes will also be useful only for equality lookup.

If you need range retrievals, your best choice is to index the
first part of the field.

In very early versions of InterBase, the test for index key size
was made at runtime, rather than index definition time. The
definition time check assumes the worst case - maximum expansion
of the key and no compression. Generally, keys don't expand to
the max, and most keys compress, so the runtime check allowed
a lot more flexibility. However, the runtime error, when it
occurred, was hard to handle. Perhaps that mode could be reintroduced
as an alternative for those who like living on the edge.

Good luck,