Performance Impacts

Basic Definitions

Normal (Single-Column) Index

A Normal Index (also called a single-column index) is created on a single column of a table. It speeds up queries that filter, sort, or join on that specific column.

CREATE INDEX idx_column_a ON table_name(column_a);

Composite (Multi-Column) Index

A Composite Index is created on two or more columns of a table. It can speed up queries that filter, sort, or join on any leading column(s) of the index.

CREATE INDEX idx_column_a_b ON table_name(column_a, column_b);

Query Performance Analysis

Let’s analyze different query scenarios using a table with columns a and b, and compare the performance impacts of different indexing strategies.

Example Table Schema

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    column_a INT,
    column_b VARCHAR(50),
    other_data TEXT
);

Scenario 1: Queries on Individual Columns

Query on Column A

SELECT * FROM example_table WHERE column_a = 10;

Performance with different indexes:

Normal index on A: Efficient access - uses the index to quickly locate rows
Normal index on B: No benefit - full table scan required
Composite index on (A,B): Efficient access - uses the leading column of the composite index
Composite index on (B,A): No benefit - the leading column B is not used in this query

Query on Column B

SELECT * FROM example_table WHERE column_b = 'value';

Performance with different indexes:

Normal index on A: No benefit - full table scan required
Normal index on B: Efficient access - uses the index to quickly locate rows
Composite index on (A,B): No benefit - cannot use the composite index since the leading column isn’t filtered
Composite index on (B,A): Efficient access - uses the leading column of the composite index

Scenario 2: Query on Both Columns A and B

SELECT * FROM example_table WHERE column_a = 10 AND column_b = 'value';

Performance with different indexes:

Normal index on A: Partial benefit - filters rows using index on A, then filters for B condition
Normal index on B: Partial benefit - filters rows using index on B, then filters for A condition
Composite index on (A,B): Maximum benefit - uses both columns in the composite index for efficient filtering
Composite index on (B,A): Maximum benefit - uses both columns in the composite index for efficient filtering

Scenario 3: Range Query on Column A and Equality on Column B

SELECT * FROM example_table WHERE column_a > 10 AND column_b = 'value';

Performance with different indexes:

Normal index on A: Partial benefit - uses index for range scan on A, then filters for B
Normal index on B: Partial benefit - uses index to find rows with B=‘value’, then filters for A
Composite index on (A,B): Partial benefit - index is used for A’s range condition but cannot fully utilize B part
Composite index on (B,A): Maximum benefit - finds exact rows where B=‘value’ and A>10

Scenario 4: Querying with OR Conditions

SELECT * FROM example_table WHERE column_a = 10 OR column_b = 'value';

When dealing with OR conditions, index usage becomes more complex. Unlike AND conditions where the database can use a single index to narrow results progressively, OR conditions require evaluating each condition separately and then combining the results.

Performance with different indexes:

Normal index on A and Normal index on B: Both indexes can be used in an “Index Merge” operation (if supported by the database)
Composite index on (A,B) or (B,A) only: Limited benefit - typically only one part of the OR condition can use the index
No indexes: Full table scan

Index Merge Operations

Many modern database systems support “Index Merge” operations that can use multiple indexes for a single query with OR conditions:

PostgreSQL:
- Uses a bitmap index scan on each index
- Combines the results using a bitmap OR operation
- Then retrieves the matching rows
MySQL:
- Can perform “Index Merge” operations combining results from multiple indexes
- Shows as “Using union(idx_a,idx_b)” in the EXPLAIN output

Example Query Plans

For PostgreSQL:

EXPLAIN ANALYZE SELECT * FROM example_table WHERE column_a = 10 OR column_b = 'value';

Example output (simplified):

BitmapOr
  ->  Bitmap Index Scan on idx_a
        Index Cond: (column_a = 10)
  ->  Bitmap Index Scan on idx_b
        Index Cond: (column_b = 'value')
->  Bitmap Heap Scan on example_table

For MySQL:

EXPLAIN SELECT * FROM example_table WHERE column_a = 10 OR column_b = 'value';

Example output (simplified):

+----+-------------+---------------+------------+-------------+----------------------+-------+------+------+--------------------------------------+
| id | select_type | table         | partitions | type        | possible_keys        | key   | rows | ... | Extra                                |
+----+-------------+---------------+------------+-------------+----------------------+-------+------+------+--------------------------------------+
|  1 | SIMPLE      | example_table | NULL       | index_merge | idx_a,idx_b          | ...   | 20   | ... | Using union(idx_a,idx_b); Using where |
+----+-------------+---------------+------------+-------------+----------------------+-------+------+------+--------------------------------------+

When Index Merge Doesn’t Apply

Index merge optimizations may not be used in certain situations:

Complex queries: With many joins or subqueries
Low selectivity: When each condition returns a large portion of the table
Database configuration: Some database settings may disable index merge operations
Older database versions: Older versions might not support this feature

Alternative Solutions for OR Conditions

If your database cannot efficiently use indexes with OR conditions, consider these alternatives:

UNION approach:

SELECT * FROM example_table WHERE column_a = 10
UNION 
SELECT * FROM example_table WHERE column_b = 'value';

This explicitly breaks the query into two separate operations, each potentially using its own index.

Functional indexes:
Some databases allow creating indexes on expressions. For example, you could create an index on a computed column or expression that consolidates the OR logic.
Materialized views or summary tables:
For frequently used OR conditions, consider creating materialized views or summary tables that pre-compute the results.

Best Practices and Guidelines

Column Order in Composite Indexes

Leading Column Rule: A composite index on (A,B) can be used for queries on column A alone, but not for queries on column B alone. The database can only use the index if the query includes predicates on a left-prefix of the index columns.
Equality First, Range Last: Put columns used with = first and range/sort columns last. The index can only seek on the columns before the first range condition; selectivity matters less than this. (Postgres 18 adds B-tree skip scan, which can use (A,B) for B = ? when A has few distinct values.)
Order by Query Pattern: Consider the most common query patterns. If you frequently filter by exact value on A and range conditions on B, create an index on (A,B) rather than (B,A).

When to Use Different Index Types

Use single-column indexes when:
- You have queries that filter on individual columns independently
- The columns have high cardinality (many distinct values)
- The storage overhead of multiple composite indexes is too high
Use composite indexes when:
- Queries frequently filter on the same combination of columns
- You want to enable covering indexes for specific queries (where all columns in the query are in the index)
- You want to optimize complex query patterns with multiple filter conditions

Index Overhead Considerations

Storage Cost: Each index increases the database storage requirements.
Maintenance Cost: Indexes slow down write operations (INSERT, UPDATE, DELETE) as they must be updated along with the table.
Query Planning Cost: Having too many indexes can make the optimizer’s job harder, sometimes leading to suboptimal execution plans.

PostgreSQL Example: Analyzing Index Usage

To see which index PostgreSQL would use for different query patterns:

-- Create indexes
CREATE INDEX idx_a ON example_table(column_a);
CREATE INDEX idx_b ON example_table(column_b);
CREATE INDEX idx_a_b ON example_table(column_a, column_b);
 
-- Analyze query plans
EXPLAIN ANALYZE SELECT * FROM example_table WHERE column_a = 10;
EXPLAIN ANALYZE SELECT * FROM example_table WHERE column_b = 'value';
EXPLAIN ANALYZE SELECT * FROM example_table WHERE column_a = 10 AND column_b = 'value';

MySQL Example: Analyzing Index Usage

To see which index MySQL would use for different query patterns:

-- Create indexes
CREATE INDEX idx_a ON example_table(column_a);
CREATE INDEX idx_b ON example_table(column_b);
CREATE INDEX idx_a_b ON example_table(column_a, column_b);
 
-- Analyze query plans
EXPLAIN SELECT * FROM example_table WHERE column_a = 10;
EXPLAIN SELECT * FROM example_table WHERE column_b = 'value';
EXPLAIN SELECT * FROM example_table WHERE column_a = 10 AND column_b = 'value';

Conclusion

When deciding between normal and composite indexes:

For queries filtering on single columns independently: Create individual indexes on each column.
For queries frequently filtering on multiple columns together: Create a composite index.
For mixed query workloads: Analyze the most critical and frequent queries, and create an appropriate mix of single-column and composite indexes.
For queries with OR conditions: Create individual indexes on each column involved in OR conditions, as modern databases can often combine them using index merge operations.

Remember that the database query optimizer will typically choose the most efficient index for a given query. However, understanding these principles helps you design indexes that give the optimizer better choices, leading to better overall database performance.

Understanding how databases handle OR conditions helps you design more efficient indexing strategies for complex query patterns involving multiple filter conditions combined with OR operators.