Limits - Firebird

Firebird allows up to 256 user-created indexes per table in version 1.5 and higher, or 64 in lower releases. However, these are theoretical limits that are governed by both page size and the actual on-disk size of the index description data on the index root page. You could not store 256 indexes in a database with a page size smaller than 16KB. On the index root page, each index needs 31 bytes for its identifier, space for descriptions of each segment (column) involved in the index, and some bytes to store a pointer to the first page of the index. Even a 16KB page may not be able to accommodate 256 indexes if there are more than a few compound indexes in the database.

To create indexes, a user must be authorized to connect to the database.

An index cannot exceed 253 bytes in total width. In reality, the number of bytes may be significantly smaller. Factors that can reduce the number of actual “slots” available to store characters include

  • International character sets that use multiple bytes per character
  • International character sets with complex uppercase/lowercase pairings and/or dictionary sorting rules
  • Use of non-binary collations
  • Multiple segments (composite indexes) that require the addition of padding bytes to retain the geometry of the index

In other words, using any character set except NONE will influence your decisions about index design—particularly whether to use composite indexes. That’s the bad news. The good news is that Firebird makes good use of single-column indexes in multi-column searches and sorts, reducing the need for many multi-column indexes that you might have experienced with another DBMS.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Firebird Topics