Is secondary index worthy?
What is secondary indexing?
It is a technique to make search faster in a database management system. Also, it is a two-index technique used to reduce the mapping size of the primary index. In other words, it is another index in the table that can be used to make queries with non-primary key fields/columns in DBMS.
Why secondary indexing?
To create an additional index other than the primary key. So that we can make queries with other columns. As it is dense by default it consumes more memory than the primary index.
How does it work?
The secondary index can be implemented with key and non-key columns. As it always maintains a dense index then does not require columns to be sorted or unsorted.
If we are applying a secondary index in key-type columns, which means a unique value column then it maintains one dense index internally.
If a non-key column encounters, it creates a sparse index because we cannot keep duplicate values as keys. In the second step, it creates a dense index for a similar key value.
It is true that a secondary index is worthy but it is also memory-consuming. One software/database developer always requires multiple indexes on a single table.
Indexing is important to implement binary search on it. The secondary index on the key column can achieve o(logn) time complexity and secondary index on non-key column can achieve o(logn+1).