Key vs Non-Key Column Indexes

Database indexes are crucial for query performance. They can be broadly categorized based on whether they are built on “key” columns (which enforce uniqueness or serve as primary identifiers) or “non-key” columns (used for general query optimization).

Example Scenario

Let’s use the following products table for our examples:

CREATE TABLE products (
    product_id INT PRIMARY KEY,       -- Key column (Primary Key)
    product_sku VARCHAR(50) UNIQUE,   -- Key column (Unique Key)
    product_name VARCHAR(255) NOT NULL,
    category_id INT,
    supplier_id INT,
    price DECIMAL(10, 2),
    is_active BOOLEAN DEFAULT TRUE,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Example of creating a non-key column index
CREATE INDEX idx_products_category_id ON products (category_id);
 
-- Example of a composite non-key column index
CREATE INDEX idx_products_supplier_price ON products (supplier_id, price);
 
-- Example of a non-key index that might be used for filtering and ordering
CREATE INDEX idx_products_last_updated ON products (last_updated DESC);

1. Key Column Indexes

These are indexes built on columns that have a special role in identifying or constraining data in a table.

a. Primary Key Index

Definition: An index automatically created (in most RDBMS) when a PRIMARY KEY constraint is defined on a column or set of columns. The primary key uniquely identifies each row in the table.
Implicit Creation: You typically don’t create a primary key index manually; it’s part of defining the primary key constraint.
- In our example: product_id INT PRIMARY KEY will automatically create a primary key index on product_id.
Characteristics:
- Uniqueness: Enforces that all values in the primary key column(s) are unique.
- Non-Null: Primary key columns cannot contain NULL values.
- Row Identification: Serves as the main way to uniquely identify and access a specific row.
- Clustering (System Dependent):
  - In some RDBMS like SQL Server, the primary key index is, by default, a clustered index. This means the physical order of data rows in the table is the same as the order of the primary key index. A table can have only one clustered index.
  - In MySQL (InnoDB), the primary key index is the clustered index: its leaf pages store the full rows, i.e. the PK index is the table. Every other index is secondary and stores the PK value to find the row.
  - In PostgreSQL, all indexes (including the primary key index) are secondary indexes that point (via ctid) to rows in a separate heap table; there is no clustered PK.
Purpose:
- Uniquely identify rows.
- Enforce entity integrity.
- Serve as the target for foreign key relationships from other tables.
- Provide fast lookups when searching for a specific row by its primary key value (e.g., SELECT * FROM products WHERE product_id = 123;).

b. Unique Key Index

Definition: An index automatically created when a UNIQUE constraint is defined on a column or set of columns. This ensures that all values in the specified column(s) are unique across the table.
Implicit Creation: Similar to primary key indexes, these are usually created as part of the constraint definition.
- In our example: product_sku VARCHAR(50) UNIQUE will automatically create a unique index on product_sku.
Characteristics:
- Uniqueness: Enforces that all values in the indexed column(s) are unique.
- Allows NULLs (Typically): Unlike primary keys, unique key columns can often allow NULL values (though the exact behavior with multiple NULLs can vary between RDBMS and how the constraint is defined).
- Secondary Identifiers: Can serve as alternative ways to uniquely identify rows.
Purpose:
- Enforce uniqueness on non-primary key columns.
- Provide alternative unique identifiers for rows.
- Enable fast lookups based on these unique values (e.g., SELECT * FROM products WHERE product_sku = 'ABC-12345';).

2. Non-Key Column Indexes (Secondary or Auxiliary Indexes)

These are indexes created on columns that are not necessarily unique and are not primary keys. Their main purpose is to improve query performance.

Definition: Indexes explicitly created by a user or DBA on one or more columns to speed up data retrieval operations, filtering (WHERE clause), sorting (ORDER BY clause), and join operations.
Explicit Creation: Created using the CREATE INDEX statement.
- In our example: CREATE INDEX idx_products_category_id ON products (category_id); creates a non-key index on the category_id column.
- CREATE INDEX idx_products_supplier_price ON products (supplier_id, price); creates a composite non-key index.
Characteristics:
- No Uniqueness Enforcement (by default): These indexes do not inherently enforce uniqueness. The indexed columns can contain duplicate values.
- Pointers to Data: A non-key index stores the indexed column value(s) and a pointer (e.g., RowID, CTID, or the primary key value if the table has a clustered primary key) to the actual data row in the table.
- Always Non-Clustered (in the sense of table organization): They do not dictate the physical storage order of the table rows themselves (unlike a clustered primary key in some systems). They are separate structures.
Purpose:
- Speed up WHERE clause filtering: Queries like SELECT * FROM products WHERE category_id = 10; can use idx_products_category_id to quickly find matching rows instead of scanning the entire table.
- Improve JOIN performance: If category_id is used in a JOIN with another table, this index can make the join faster.
- Optimize ORDER BY and GROUP BY: Queries like SELECT category_id, COUNT(*) FROM products GROUP BY category_id; or SELECT * FROM products ORDER BY last_updated DESC; can benefit from indexes on category_id and last_updated respectively, potentially avoiding costly sort operations.
- Covering Indexes: A powerful type of non-key index. If an index contains all the columns requested by a query (both in the SELECT list and the WHERE clause), the database can answer the query by reading only the index, without accessing the table itself. This is called an index-only scan and is very efficient.
  - Example: For the query SELECT supplier_id, price FROM products WHERE supplier_id = 5;, the index idx_products_supplier_price could potentially be a covering index if the database optimizer chooses to use it this way.

3. Key Differences Summarized

Feature	Key Column Indexes (Primary/Unique)	Non-Key Column Indexes (Secondary)
Primary Purpose	Uniqueness, Row Identification, Data Integrity	Query Performance Optimization
Uniqueness	Enforced (Primary Key: unique & non-null; Unique Key: unique)	Not enforced by default (allows duplicates)
NULL Values	Primary Key: Not allowed. Unique Key: Usually allowed.	Allowed (unless column constraint prevents it)
Creation	Often implicit with `PRIMARY KEY` or `UNIQUE` constraints	Explicit via `CREATE INDEX` statement
Table Clustered By	Primary Key often dictates clustering in some RDBMS	Typically non-clustered (separate from table data order)
Number Per Table	One Primary Key. Multiple Unique Keys.	Multiple non-key indexes allowed.

4. Considerations

Write Overhead: All indexes (key or non-key) add overhead to write operations (INSERT, UPDATE, DELETE). When data is modified, the corresponding indexes must also be updated. Too many indexes can slow down writes.
Storage Space: Indexes consume disk space.
Selectivity: Indexes are most effective on columns with high selectivity (many distinct values relative to the total number of rows). Indexes on low-selectivity columns (e.g., a boolean is_active column in a very large table where most values are TRUE) might not be used by the optimizer or could even be detrimental, though bitmap indexes can be useful here.
Query Patterns: Choose indexes based on the actual queries your application runs. Analyze slow queries to identify candidate columns for indexing.
Maintenance: Indexes may require maintenance (e.g., rebuilding or reorganizing) to remain efficient over time as data changes.

Understanding the distinction between key and non-key column indexes helps in designing efficient database schemas and optimizing query performance effectively.

Kaushik's Notes

Explorer