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

Ann W. Harrison answers:

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.

Like this post? Share on: TwitterFacebookEmail

Related Articles


Firebird Community



Gems from Firebird Support list