Master Database Indexes: 10 Essential Questions Every Developer Should Know

1. What is an index? Why do we need it?

1.1 The essence of an index

In simple terms, an index is a directory of data, similar to a book's table of contents, allowing fast location of rows.

-- Full table scan (no index)
SELECT * FROM users WHERE name = '苏三';
-- Index scan
CREATE INDEX idx_name ON users(name);
SELECT * FROM users WHERE name = '苏三';  -- uses index

1.2 How indexes work

Underlying structure (B+Tree):

2. Ten common index questions

1. Why is my indexed query still slow?

Scenario :

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

Analysis :

Leading wildcard : LIKE '%苏三%' disables the index.

Low selectivity : many duplicate names.

Back‑to‑table : index does not cover all needed columns.

Solutions :

-- 1. Avoid leading wildcard
SELECT * FROM users WHERE name LIKE '苏三%';

-- 2. Use covering index
CREATE INDEX idx_name_covering ON users(name, id, email);
SELECT name, id, email FROM users WHERE name = '苏三';

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

2. Are more indexes always better?

Absolutely not. Each index adds storage cost and slows writes.

INSERT INTO users (name, email, age) VALUES ('苏三', '[email protected]', 30);
-- Indexes on primary key, idx_name, idx_email, idx_age must be updated

Storage space consumption.

Write performance degradation.

Optimizer burden.

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

3. What is the left‑most prefix rule for composite indexes?

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 = '苏三';                     -- uses index
SELECT * FROM users WHERE name = '苏三' AND age = 30;        -- uses index
SELECT * FROM users WHERE age = 30;                        -- cannot use index

4. How to choose column order in a composite index?

Principles:

High‑selectivity columns first.

Frequently queried columns first.

Equality columns before range columns.

-- Calculate 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;

-- Example 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 needed by the query, eliminating the need to read the table.

-- Non‑covering index (needs back‑to‑table)
CREATE INDEX idx_name ON users(name);
SELECT * FROM users WHERE name = '苏三';

-- Covering index (no back‑to‑table)
CREATE INDEX idx_name_covering ON users(name, email, age);
SELECT name, email, age FROM users WHERE name = '苏三';

Avoid back‑to‑table → less I/O.

Reduced memory usage.

Better performance.

6. How do NULL values affect indexes?

Indexes may not be used for IS NULL / IS NOT NULL predicates.

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 :

Set default values.

Use functional index (MySQL 8+):

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?

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

8. How to detect index misuse?

Common pitfalls: functions on columns, type conversion, arithmetic, leading wildcard.

-- Example of index not used
SELECT * FROM users WHERE YEAR(create_time) = 2023;
SELECT * FROM users WHERE phone = 13800138000;   -- phone is varchar
SELECT * FROM users WHERE age + 1 > 30;
SELECT * FROM users WHERE name LIKE '%苏三';

Use EXPLAIN to inspect type, key, rows, Extra.

EXPLAIN SELECT * FROM users WHERE name = '苏三';
-- Look at type (const|ref|range|index|ALL), key, rows, Extra

9. How to maintain and optimise indexes?

-- View 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;

-- Analyse index statistics
ANALYZE TABLE users;

Oracle example:

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

10. Differences between MySQL and PostgreSQL indexes

Feature

MySQL

PostgreSQL

Index types

B+Tree, Hash, Fulltext

B+Tree, Hash, GiST, SP‑GiST

Covering index

Supported

Supported (INCLUDE)

Functional index

8.0+ supported

Supported

Partial index

Supported

Supported

Index‑organized table

Clustered index

Heap table

-- PostgreSQL covering index
CREATE INDEX idx_users_covering ON users(name) INCLUDE (email, age);

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

-- PostgreSQL expression index
CREATE INDEX idx_name_lower ON users(LOWER(name));

3. Index design best practices

3.1 Design principles

Create indexes only for frequently queried columns.

Choose appropriate index type (B‑Tree, Hash, full‑text, etc.).

Consider composite indexes to reduce total count.

Avoid over‑indexing; each index has maintenance cost.

Regularly rebuild or analyse indexes.

3.2 Design checklist

Conclusion

Understand the B+Tree mechanism.

Design indexes following the left‑most prefix rule.

Watch out for common index‑invalidating patterns.

Prefer covering indexes to avoid back‑to‑table lookups.

Monitor usage and periodically optimise.

Balance query speed against write overhead.

Good index design is the cornerstone of database performance.

Do not add indexes blindly; base them on real query patterns and data distribution.