Mastering Database Indexes: 10 Essential Questions Every Developer Should Know

Preface

Today we discuss the love‑and‑hate relationship developers have with database indexes.

Why does a query stay slow even after adding an index?

Why can an index sometimes degrade performance?

How should a composite index be designed?

Through ten questions we will uncover the mysteries of indexes.

1. What is an index and why do we need it?

1.1 Index essence

An index is essentially a data directory that lets the database locate rows quickly, just like a book’s table of contents.

-- No index (full table scan)
SELECT * FROM users WHERE name = 'Susan';
-- With index (index scan)
CREATE INDEX idx_name ON users(name);
SELECT * FROM users WHERE name = 'Susan';

1.2 How indexes work

Indexes are stored as B+ trees.

The underlying structure is a B+ tree.

2. Ten common index questions

2.1 Why is the query still slow?

Scenario: Adding an index but the query remains slow.

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

Root cause:

Leading wildcard disables index usage.

Low selectivity of the indexed column.

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

Solution:

-- Avoid leading wildcard
SELECT * FROM users WHERE name LIKE 'Susan%';
-- Use covering index
CREATE INDEX idx_name_covering ON users(name, email, age);
SELECT name, email, age FROM users WHERE name = 'Susan';

2.2 Are more indexes always better?

Indexes incur maintenance costs: extra storage, slower INSERT/UPDATE/DELETE, and higher optimizer complexity. A practical rule is to keep the number of indexes per table under 5‑7.

2.3 Composite index left‑most prefix principle

Composite indexes can only be used starting from the leftmost column.

CREATE INDEX idx_name_age ON users(name, age);
SELECT * FROM users WHERE name = 'Susan';          -- uses index
SELECT * FROM users WHERE age = 30;               -- cannot use index

2.4 How to choose field order in a composite index?

Guidelines:

Place high‑selectivity columns first.

Put frequently queried columns first.

Equality conditions first, range conditions later.

2.5 What is a covering index and why is it important?

A covering index contains all columns required by the query, eliminating the need for a table lookup.

-- Non‑covering index (needs 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';

2.6 How do NULL values affect indexes?

Queries with IS NULL or IS NOT NULL may not use the index. Solutions include avoiding NULLs, setting default values, or using functional indexes (MySQL 8.0+).

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

2.7 Index impact on ORDER BY and GROUP BY

An index that matches the ordering columns can avoid filesort; similarly, an index on grouping columns speeds up aggregation.

CREATE INDEX idx_age_name ON users(age, name);
SELECT * FROM users ORDER BY age, name;   -- uses index
SELECT age, COUNT(*) FROM users GROUP BY age; -- uses index

2.8 How to detect index inefficiency?

Common scenarios that invalidate indexes include function calls, type casts, arithmetic operations, and leading wildcards. Use EXPLAIN to inspect the execution plan.

EXPLAIN SELECT * FROM users WHERE name = 'Susan';

2.9 How to maintain and optimize indexes?

Regularly monitor index usage, rebuild fragmented indexes, and analyze statistics.

-- Check usage (MySQL)
SELECT * FROM sys.schema_index_statistics WHERE table_schema='your_db' AND table_name='users';
-- Rebuild index
ALTER TABLE users REBUILD INDEX idx_name;
-- Update statistics
ANALYZE TABLE users;

2.10 Differences between databases

MySQL supports B+Tree, Hash, Full‑text indexes; PostgreSQL adds GiST, SP‑GiST, and INCLUDE for covering indexes. Function and partial indexes are available in both, with syntax differences.

Feature

MySQL

PostgreSQL

Index types

B+Tree, Hash, Fulltext

B+Tree, Hash, GiST, SP‑GiST

Covering index

Supported

Supported via INCLUDE

Functional index

8.0+ supported

Supported

Partial index

Supported

Supported

Clustered table

Clustered index

Heap table

3. Index design best practices

3.1 Design principles

Create indexes only for columns that are frequently queried.

Choose the appropriate index type for the workload.

Prefer composite indexes to reduce total index count.

Avoid excessive indexing due to maintenance overhead.

Regularly rebuild and analyze indexes.

3.2 Design checklist

Conclusion

Understand the B+Tree mechanics behind indexes.

Design indexes following the left‑most prefix rule.

Avoid common pitfalls that cause index loss.

Leverage covering indexes to eliminate table lookups.

Monitor and maintain indexes regularly.

Balance query speed gains against write‑cost overhead.

Good index design is the cornerstone of database performance.

Do not add indexes blindly; base your design on actual query patterns and data distribution.