Practical Guide to Database Indexing: When and How to Create Effective Indexes

Introduction

Many developers create indexes without understanding when they actually improve performance; this guide summarizes the most useful indexing strategies and how to apply them correctly.

Key Indexing Scenarios

1. Create indexes on fields that are frequently used in query conditions

Columns appearing often in WHERE clauses should be indexed to avoid full‑table scans as data grows.

CREATE INDEX idx_status ON table_name(status);
SELECT * FROM table_name WHERE status = 1;

Typical queries that can use the index include:

Equality queries, e.g., WHERE column_name = 'value'

Range queries, e.g., WHERE column_name BETWEEN 'value1' AND 'value2' or WHERE column_name > 'value'

Partial (right‑side) fuzzy queries, e.g., WHERE column_name LIKE 'value%'

Sorting and grouping when the sorted/ grouped column is indexed

Join queries when the join columns are indexed

2. Create a unique index for fields that identify a single record

A unique index allows the database to locate the exact row without scanning.

CREATE UNIQUE INDEX idx_uid ON table_name(uid);
SELECT * FROM table_name WHERE uid = 123;

3. Index foreign‑key columns used in table joins

Joining tables on indexed keys avoids full scans of the right‑hand table.

CREATE INDEX idx_post_id ON comments(post_id);
SELECT * FROM comments WHERE post_id = 12345;

4. Index columns that are often used for sorting

When sorting large tables, an index can provide rows in order directly.

CREATE INDEX idx_create_time ON table_name(create_time);
SELECT * FROM table_name ORDER BY create_time DESC;

5. Index columns frequently used in LEFT JOIN operations

Left‑join queries benefit from indexes on the joining columns.

CREATE INDEX idx_user_id ON table_a(user_id);
SELECT * FROM table_a LEFT JOIN table_b ON table_a.user_id = table_b.user_id;

6. Ensure the primary key has an index for large tables

The primary key is indexed by default; removing it degrades all CRUD operations.

CREATE INDEX idx_id ON large_table(id);

7. Use composite (joint) indexes for multi‑column queries

When single‑column filters are insufficient, a composite index can improve performance for combined conditions.

Example table products(product_id, product_name, category) :

CREATE INDEX idx_product_name_category ON products(product_name, category);

Query using the composite index:

SELECT product_name FROM products WHERE product_name LIKE 'xyz%' AND category = 'some_category';

8. Create prefix indexes for text columns

Prefix indexes help with right‑side fuzzy searches and can be combined with other strategies such as external search engines.

CREATE INDEX idx_title_prefix ON table(title(10));
SELECT * FROM table WHERE title LIKE 'key%';

Conclusion

After applying the appropriate indexes, use the EXPLAIN statement to verify that the optimizer actually utilizes them.