COVERING INDEX / INCLUDE COLUMNSIndexes & Search
An index that contains all columns needed to satisfy a query, allowing the engine to return results from the index alone without accessing the base table.
An index that includes all columns a query needs, allowing the planner to satisfy the query from the index alone without touching the table (index-only scan). Add frequently-selected columns to an index with INCLUDE for covering without changing key column order.
| Database System | Support Status | Since Version | Notes |
|---|---|---|---|
| MySQL | ✓ Supported | 5.0 | InnoDB supports covering indexes implicitly — any index that contains all required columns satisfies the query from the index (an 'index-only scan'). No INCLUDE syntax; simply add the extra columns to the index key. The primary key is always appended to secondary index leaf nodes in InnoDB. |
| PostgreSQL | ✓ Supported | 11 | INCLUDE clause added in PG 11 for non-key columns in B-tree indexes. Non-key columns are stored only in leaf pages and cannot be used in index conditions, but satisfy column lookups without a heap fetch. |
| SQL Server | ✓ Supported | 2005 | INCLUDE columns are added to the leaf level of the index only. This is more space-efficient than adding them as key columns and avoids the 900-byte key size limit. Covering indexes with INCLUDE are a core SQL Server tuning technique. |
| Oracle | ✓ Supported | 8i | Oracle achieves covering index behavior differently: an index-organized table (IOT) stores all data in the index structure. For secondary indexes, adding extra columns to the key achieves the same read effect. No INCLUDE syntax — all indexed columns are key columns. |
| SQLite | ✓ Supported | 3.38.0 | SQLite added INCLUDE column support in 3.38.0 (2022). Prior to that, covering index behavior was achievable only by including extra columns as key columns. |
Covering indexes are one of the most impactful query tuning tools available — eliminating the table lookup (heap fetch / row fetch) for selective queries can reduce I/O by an order of magnitude. The INCLUDE syntax (PostgreSQL, SQL Server, SQLite) is preferable to adding columns to the key because non-key columns don't affect key ordering or the index key size limit, and they consume space only in leaf nodes. MySQL and Oracle achieve the same effect by including extra columns in the key, but this makes those columns part of the sort order and counts against key size limits. In SQL Server, the query optimizer will flag 'missing index' hints that suggest INCLUDE columns — these are worth reviewing but should not be applied blindly without considering index maintenance overhead.
InnoDB supports covering indexes implicitly — any index that contains all required columns satisfies the query from the index (an 'index-only scan'). No INCLUDE syntax; simply add the extra columns to the index key. The primary key is always appended to secondary index leaf nodes in InnoDB.
INCLUDE clause added in PG 11 for non-key columns in B-tree indexes. Non-key columns are stored only in leaf pages and cannot be used in index conditions, but satisfy column lookups without a heap fetch.
INCLUDE columns are added to the leaf level of the index only. This is more space-efficient than adding them as key columns and avoids the 900-byte key size limit. Covering indexes with INCLUDE are a core SQL Server tuning technique.
Oracle achieves covering index behavior differently: an index-organized table (IOT) stores all data in the index structure. For secondary indexes, adding extra columns to the key achieves the same read effect. No INCLUDE syntax — all indexed columns are key columns.
SQLite added INCLUDE column support in 3.38.0 (2022). Prior to that, covering index behavior was achievable only by including extra columns as key columns.