Yes — in most databases, you can control whether an index contains additional data or not — by choosing which columns to include in the index. Let’s go over how you can manage this in different databases.
✅ 1. In PostgreSQL
🎯 Every index contains:
- The indexed column(s).
- A row pointer to the actual table row.
🎯 You can also add more columns to store extra data using INCLUDE (PostgreSQL 11+):
CREATE INDEX idx_email_include_name ON users(email) INCLUDE (name);
email is the indexed column (used for filtering and sorting)
name is included (stored in the index but not sorted)
✅ Benefits of INCLUDE:
- You can make index-only scans possible without affecting index size too much.
- Better performance for some queries without bloating sort keys.
✅ 2. In MySQL (InnoDB)
In MySQL, you manage it manually by including extra columns in the index:
CREATE INDEX idx_email_name ON users(email, name);
This makes the index a covering index for queries like:
SELECT name FROM users WHERE email = 'stanley@example.com';
MySQL does not have INCLUDE like PostgreSQL. If you include extra columns, they are also part of the index key — which affects sorting and size.
✅ However, it still enables index-only reads when all columns are present.
✅ 3. SQL Server
SQL Server does support INCLUDE columns explicitly:
CREATE INDEX idx_email ON users(email) INCLUDE (name);
email= index keyname= stored in the index leaf, not used for sorting or searching
Very similar to PostgreSQL’s INCLUDE.
📌 Summary: How to Control Data in Index
| Database | Control Extra Columns? | How to Include Extra Data |
|---|---|---|
| PostgreSQL | ✅ Yes | Use INCLUDE after index columns |
| MySQL (InnoDB) | ✅ Yes (manually) | Add extra columns to the index definition |
| SQL Server | ✅ Yes | Use INCLUDE clause |
| SQLite | ❌ No | No built-in INCLUDE, only full keys |
🧠 When Should You Include Extra Columns?
✅ Use it when:
- You want to enable index-only scans
- You run frequent read queries that only need those columns
- You want to avoid accessing the full row (heap access)