10 Proven Index Design Rules to Supercharge Database Performance

Preface

In large‑scale systems, poorly designed indexes cause over 60% of performance bottlenecks. After years of handling database incidents, the author distilled ten core principles: an index should be precise rather than abundant.

1. Understand Business Scenarios

Understanding the business scenario is the foundation of index design.

Wrong Example: Adding Indexes Blindly

-- 未分析业务场景就创建索引
CREATE INDEX idx_all_columns ON orders (customer_id, product_id, status, create_time);

Correct Practice: Business‑Scenario Analysis Matrix

A typical matrix lists query type, frequency, response requirement, data volume, and suggested index. For example, high‑frequency user‑order queries (<100 ms, millions of rows) benefit from an index on

(user_id, status)

.

Business‑scenario analysis flowchart:

Deep Insight: An e‑commerce system reduced order‑query latency from 2 s to 50 ms, boosting TPS by 300% after proper index analysis.

2. Left‑most Prefix Principle

The left‑most prefix rule is the soul of composite indexes.

Index Structure Analysis

Query Matching Rules

-- Index hit
SELECT * FROM orders WHERE user_id = 1001 AND status = 'PAID';

-- Index hit (left‑most prefix)
SELECT * FROM orders WHERE user_id = 1001;

-- Index miss (violates left‑most prefix)
SELECT * FROM orders WHERE status = 'PAID';

Principle Analysis: Composite indexes are built as B+ trees in the declared order; missing the leftmost column prevents index usage.

3. Avoid Over‑Indexing

Excessive indexes are hidden killers for write operations.

Index Cost Formula

Write cost = data write + Σ(index write)

Index Impact Experiment

-- Test table
CREATE TABLE test_table (
  id INT PRIMARY KEY,
  col1 VARCHAR(20),
  col2 VARCHAR(20),
  col3 VARCHAR(20)
);

-- Insert performance without index: 0.5 ms
INSERT INTO test_table ...;

-- After adding one index: 0.8 ms
CREATE INDEX idx1 ON test_table(col1);
INSERT INTO test_table ...;

-- After adding three indexes: 1.8 ms
CREATE INDEX idx2 ON test_table(col2);
CREATE INDEX idx3 ON test_table(col3);
INSERT INTO test_table ...;

Index write latency chart:

Golden Rule: Keep the number of indexes per table under 5 and columns per index under 3.

4. Covering Index

A covering index is the ultimate performance boost.

Without Covering Index

EXPLAIN SELECT order_no, amount FROM orders WHERE user_id = 1001 AND status = 'PAID';

Execution plan shows a regular index lookup.

With Covering Index

-- Create covering index
CREATE INDEX idx_covering ON orders(user_id, status, order_no, amount);

EXPLAIN SELECT order_no, amount FROM orders WHERE user_id = 1001 AND status = 'PAID';

Execution plan shows

Using index

, eliminating row lookups.

Performance Comparison: Covering indexes reduce disk I/O and speed up queries by 5‑10×.

5. Data Type Optimization

Choosing the right data type reduces index size.

Typical storage per million rows:

BIGINT – 8 bytes → 15 MB index

INT – 4 bytes → 7.5 MB index

MEDIUMINT – 3 bytes → 5.6 MB index

CHAR(32) – 32 bytes → 61 MB index

VARCHAR(32) – variable → 20‑50 MB index

Optimization Case

-- Before: store IP as string
CREATE TABLE access_log (
  id BIGINT,
  ip VARCHAR(15),
  INDEX idx_ip (ip)
);

-- After: store IP as integer
CREATE TABLE access_log (
  id BIGINT,
  ip INT UNSIGNED,
  INDEX idx_ip (ip)
);

Space Savings: IP index size drops from 78 MB to 12 MB, improving memory hit rate by 40%.

6. Function Traps

Functions on indexed columns cause index loss.

Index Failure Example

-- Create index on create_time
CREATE INDEX idx_create_time ON orders(create_time);

-- Query with function (index miss)
SELECT * FROM orders WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2023-06-01';

-- Optimized query (index hit)
SELECT * FROM orders WHERE create_time BETWEEN '2023-06-01 00:00:00' AND '2023-06-01 23:59:59';

Function Usage Principle

graph LR
    A[Query condition] --> B{Contains function?}
    B -->|Yes| C[Index may fail]
    B -->|No| D[Use index normally]
    C --> E[Rewrite condition]
    E --> D

Performance Comparison: Date‑range query time drops from 1200 ms to 15 ms after removing the function.

7. Prefix Index

Prefix indexes rescue large text fields.

Creation Method

-- Original field index (fails for TEXT)
CREATE INDEX idx_product_desc ON products(description);

-- Prefix index (first 20 characters)
CREATE INDEX idx_product_desc_prefix ON products(description(20));

Length Selection Algorithm

SELECT
  COUNT(DISTINCT LEFT(description,10))/COUNT(*) AS selectivity10,
  COUNT(DISTINCT LEFT(description,20))/COUNT(*) AS selectivity20,
  COUNT(DISTINCT LEFT(description,30))/COUNT(*) AS selectivity30
FROM products;

Recommended length: 20 characters (selectivity 0.92).

Space Savings: Index size for 5 million rows drops from 1.2 GB to 120 MB.

8. NULL Value Handling

NULLs act as ghosts in indexes.

Problematic Index with NULL

-- Index on nullable column
CREATE INDEX idx_email ON users(email);

SELECT * FROM users WHERE email IS NULL;  -- May not use index

Optimization

ALTER TABLE users MODIFY email VARCHAR(255) NOT NULL DEFAULT '';

Best practice: define important query columns as

NOT NULL DEFAULT

with a suitable default value.

9. Index Maintenance

Regular maintenance keeps performance stable.

Maintenance Scripts

-- Rebuild fragmented index
ALTER TABLE orders REBUILD INDEX idx_user_status;

-- Update statistics
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 32 BUCKETS;

-- Monitoring script
SELECT index_name,
       ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb,
       index_type,
       table_rows
FROM mysql.innodb_index_stats
WHERE table_name = 'orders';

Fragmentation impact curve:

Maintenance Advice: Rebuild indexes monthly; if fragmentation exceeds 30%, rebuild immediately.

10. Monitoring & Tuning

Monitoring and tuning manage the index lifecycle.

Unused Index Detection

SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema NOT IN ('mysql','sys');

Index monitoring dashboard:

Real Case: A financial system removed over 200 unused indexes, improving write performance by 50%.

Conclusion

Business‑Driven: Index design starts with business‑scenario analysis.

Left‑most First: Composite indexes must obey the left‑most prefix rule.

Moderate Simplicity: Beware of write amplification caused by excessive indexes.

Covering Is King: Prefer covering indexes to eliminate row lookups.

Type Optimization: Use small, precise data types to shrink index size.

Function Avoidance: Do not apply functions on indexed columns.

Prefix Compression: Use prefix indexes for large text fields.

NULL Handling: Avoid NULLs on important query columns.

Regular Maintenance: Establish an index maintenance routine.

Continuous Monitoring: Build a lifecycle management system for indexes.