How do I create an index?
Creating an index is easy. We can create an index using the CREATE INDEX command and specify an index type available.
CREATE INDEX "sector tracker" ALT3 ON financial.companies (sector)
For an explanation of index types look at the indexes page.
How do write operation affect indexes?
Write operations may require updates to indexes:
- If a write operation modifies an indexed column, MDB updates all indexes that have the modified field as a key.
Therefore, if your application is write-heavy, indexes might affect performance.
How does multi-version data affect indexes?
TRADITIONAL and SPARSE blockchains require primary keys and allow both AMEND and DISCONTINUE mutations. In order to keep track of the current state of the blockchain while supporting fast access to record history, a special index called a Multi-Version Index is built on the primary key column of the blockchain. This secondary index, keeps track of the current state in the index leaves, additionally index leaves point to cluster pages that track previous states.
- Cluster pages aren't brought into memory unless record history is being read.
- Read operations on the current state aren't affected by the cluster pages.
- Scanning both leaves and their corresponding cluster pages guarantees a sorted order.
Multi-Version Indexes help keep traditional application speeds while incorporating the added functionality of multi-version data.