Master Database Indexes: 10 Essential Questions and Best Practices

Preface

Many developers love and hate database indexes. This article answers ten common questions to help you master indexes.

1. What Is an Index and Why Do We Need It?

1.1 Essence of an Index

An index is essentially a directory for data, similar to a book's table of contents, allowing the database to locate rows quickly.

1.2 How Indexes Work (B+‑Tree)

The underlying structure of most relational indexes is a B+‑tree.

-- Query without an index (full table scan)
SELECT * FROM users WHERE name = 'Susan';

-- Create an index on the name column
CREATE INDEX idx_name ON users(name);

-- Query with the index (index scan)
SELECT * FROM users WHERE name = 'Susan';

The B+‑tree stores keys (e.g., name) in sorted order, enabling logarithmic‑time lookups.

2. Ten Common Index Questions

1) Why Is My Query Still Slow After Adding an Index?

Scenario :

CREATE INDEX idx_name ON users(name);
SELECT * FROM users WHERE name LIKE '%Susan%'; -- still slow

Reasons :

Leading wildcard ( LIKE '%Susan%') prevents index usage.

Low selectivity – many rows share the same name.

Index does not cover all needed columns, causing a costly table lookup.

Solutions :

-- Avoid leading wildcard
SELECT * FROM users WHERE name LIKE 'Susan%';

-- Use a covering index
CREATE INDEX idx_name_covering ON users(name, id, email);
SELECT name, id, email FROM users WHERE name LIKE 'Susan%';

-- Full‑text index for flexible text search
CREATE FULLTEXT INDEX ft_name ON users(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('Susan');

2) Are More Indexes Always Better?

Indexes incur maintenance costs. Each additional index slows INSERT/UPDATE/DELETE because the engine must update the index structures.

-- Insert example showing index updates
INSERT INTO users (name, email, age) VALUES ('Susan', '[email protected]', 30);
-- Affected indexes: primary key, idx_name, idx_email, idx_age, etc.

Typical guidelines:

Storage overhead – each index consumes disk space.

Write performance – more indexes mean slower writes.

Optimizer complexity – too many indexes make plan selection harder.

Rule of thumb : keep the total number of indexes per table under 5‑7.

3) What Is the Left‑most Prefix Rule for Composite Indexes?

A composite index can be used only from its left‑most column onward.

-- Composite index on (name, age)
CREATE INDEX idx_name_age ON users(name, age);

-- Queries that can use the index
SELECT * FROM users WHERE name = 'Susan';          -- uses index
SELECT * FROM users WHERE name = 'Susan' AND age=30; -- uses index

-- Query that cannot use the index
SELECT * FROM users WHERE age = 30;               -- cannot use index

4) How Should I Order Columns in a Composite Index?

Guidelines:

Place high‑selectivity columns first.

Put frequently queried columns first.

Equality columns before range columns.

-- Compute selectivity
SELECT COUNT(DISTINCT name)/COUNT(*) AS name_sel,
       COUNT(DISTINCT age)/COUNT(*) AS age_sel,
       COUNT(DISTINCT city)/COUNT(*) AS city_sel
FROM users;

-- Build index based on selectivity
CREATE INDEX idx_name_city_age ON users(name, city, age);

5) What Is a Covering Index and Why Is It Important?

A covering index contains all columns required by the query, eliminating the need to read the table rows.

-- Non‑covering index (requires table lookup)
CREATE INDEX idx_name ON users(name);
SELECT * FROM users WHERE name = 'Susan';

-- Covering index (no table lookup)
CREATE INDEX idx_name_covering ON users(name, email, age);
SELECT name, email, age FROM users WHERE name = 'Susan';

Benefits:

Avoids extra I/O (no row lookup).

Reduces memory usage.

Improves query speed.

6) How Do NULL Values Affect Indexes?

Indexes may not be used for columns containing NULLs, depending on the query.

CREATE INDEX idx_email ON users(email);
SELECT * FROM users WHERE email IS NULL;      -- may not use index
SELECT * FROM users WHERE email IS NOT NULL;  -- may not use index

Solutions:

Avoid NULLs by defining default values.

Use functional indexes (MySQL 8.0+):

CREATE INDEX idx_email_null ON users((COALESCE(email, '')));
SELECT * FROM users WHERE COALESCE(email, '') = '';

7) How Do Indexes Influence ORDER BY and GROUP BY?

An index that matches the ORDER BY or GROUP BY columns can eliminate sorting.

-- Index to support ordering
CREATE INDEX idx_age_name ON users(age, name);
SELECT * FROM users ORDER BY age, name;   -- uses index, no filesort

-- Index to support grouping
SELECT age, COUNT(*) FROM users GROUP BY age; -- uses index

When the column order does not follow the left‑most rule, the optimizer cannot use the index for sorting.

8) How to Detect Index Inefficiency?

Common scenarios where indexes become ineffective:

Functions on indexed columns (e.g., WHERE YEAR(create_time)=2023).

Implicit type conversion (e.g., WHERE phone = 13800138000 when phone is VARCHAR).

Arithmetic operations (e.g., WHERE age + 1 > 30).

Leading wildcards ( WHERE name LIKE '%Susan').

Use EXPLAIN to inspect the execution plan:

EXPLAIN SELECT * FROM users WHERE name = 'Susan';
-- Look at type, key, rows, Extra (Using index, Using filesort, etc.)

9) How to Maintain and Optimize Indexes?

Regular maintenance steps:

-- Check index usage (MySQL)
SELECT * FROM sys.schema_index_statistics
WHERE table_schema='your_database' AND table_name='users';

-- Rebuild fragmented index
ALTER TABLE users REBUILD INDEX idx_name;

-- Analyze index statistics
ANALYZE TABLE users;

For Oracle, enable index monitoring:

ALTER INDEX idx_name MONITORING USAGE;
SELECT * FROM v$object_usage WHERE index_name='IDX_NAME';

10) How Do Indexes Differ Across Databases?

Key differences between MySQL and PostgreSQL:

Supported index types: MySQL – B+Tree, Hash, Fulltext; PostgreSQL – B+Tree, Hash, GiST, SP‑GiST.

Covering indexes: MySQL supports them directly; PostgreSQL uses INCLUDE clause.

Functional indexes: both support, PostgreSQL has broader expression support.

Partial indexes: available in both.

Index‑organized tables: MySQL uses clustered indexes; PostgreSQL stores data in heap tables.

PostgreSQL examples:

-- Covering index with INCLUDE
CREATE INDEX idx_users_covering ON users(name) INCLUDE (email, age);

-- Partial index
CREATE INDEX idx_active_users ON users(name) WHERE is_active = true;

-- Expression index (lowercase name)
CREATE INDEX idx_name_lower ON users(LOWER(name));

3. Index Design Best Practices

3.1 Design Principles

Create indexes only for columns that are frequently queried.

Choose the appropriate index type (B‑Tree, Hash, Full‑text, etc.) based on workload.

Prefer composite indexes to reduce the total number of indexes.

Avoid over‑indexing; each index adds write overhead.

Schedule regular maintenance: rebuild fragmented indexes and analyze statistics.

3.2 Design Checklist

(Checklist image omitted for brevity.)

Conclusion

Understand the B+‑tree mechanics behind indexes.

Design indexes following the left‑most prefix rule and column ordering guidelines.

Avoid common pitfalls that cause index loss.

Leverage covering indexes to eliminate table lookups.

Monitor usage and perform periodic maintenance.

Balance query speed against write cost – more indexes are not always better.

Good index design is the cornerstone of database performance.

Indexes should be added based on actual query patterns and data distribution, not arbitrarily.