Secondary index
A secondary index is a separate access structure that supports searches without defining the table's main row-storage organization.
Secondary describes separation from the table's row storage, not whether an index enforces a primary key. All PostgreSQL indexes are secondary in this sense. A covering index may supply query values without a heap read, but visibility checks can still require one. [1]
For an InnoDB table with a declared primary key, secondary records include that key as the locator for the clustered row. A wide primary key therefore enlarges secondary-index storage too. [2]
SQLite secondary indexes use rowid for ordinary tables and primary-key columns for WITHOUT ROWID tables. In a WITHOUT ROWID secondary index, a key column already present with the same collation is not repeated in the locator suffix. The locator representation is engine-specific, not universally a physical address. [3]
Relations
- related
- Clustered index, B-tree, SQLite WITHOUT ROWID table, Covering index, Index-only scan (PostgreSQL)
Citations
- PostgreSQL 18: Index-Only Scans and Covering Indexes
- MySQL 8.4: Clustered and Secondary Indexes
- SQLite file format: table storage and secondary-index row locators
Sources & caveats
- Terminology is scoped by storage organization rather than SQL constraint names. Existing SQLite metadata controls illustrate row locators; they do not measure total index bytes or a speedup.