Best Practices for Choosing Database Indexes

1. Choose Unique Indexes

Unique index values are distinct, allowing rapid location of a specific record—for example, a student ID field. Indexing a unique column speeds up lookups, whereas using a non‑unique column like a name can cause duplicate matches and slower queries.

2. Index Fields Frequently Used for Sorting, Grouping, and Joins

Columns that are often involved in ORDER BY , GROUP BY , DISTINCT , or UNION operations benefit from indexing, as it eliminates costly sorting steps.

3. Index Columns Commonly Used as Query Conditions

If a column is regularly used in WHERE clauses, indexing it improves the overall query performance of the table.

4. Limit the Number of Indexes

More indexes consume additional disk space and make index maintenance (rebuilding, updating) slower; excessive indexes can significantly degrade write performance.

5. Prefer Indexes on Small‑Size Columns

Long column values (e.g., CHAR(100) ) slow down lookups compared to shorter columns like CHAR(10) .

6. Use Prefix Indexes for Long Text Fields

When indexing long TEXT or BLOB columns, indexing only the leading characters (a prefix) can greatly improve search speed.

7. Remove Unused or Rarely Used Indexes

When data patterns change, some indexes become unnecessary; regularly dropping such indexes reduces update overhead.

8. Follow the Left‑Most Prefix Matching Principle

MySQL uses the leftmost columns of a composite index until it encounters a range condition (e.g., > , < , BETWEEN , LIKE ). The order of columns in the index therefore matters.

9. Equality and IN Conditions Can Be Reordered

For statements like a = 1 AND b = 2 AND c = 3 , an index on (a,b,c) can be used regardless of the order; the optimizer rearranges the conditions to match the index.

10. Choose High‑Cardinality Columns as Indexes

Cardinality is calculated as COUNT(DISTINCT col) / COUNT(*) . Higher cardinality means fewer rows need to be scanned; a unique key has cardinality 1, while low‑cardinality columns (e.g., gender) may have near‑zero usefulness. As a rule of thumb, columns used in joins should have cardinality above 0.1.

11. Keep Indexed Columns “Clean” (No Functions)

Applying functions to indexed columns prevents index usage. For example, FROM_UNIXTIME(create_time) = '2014-05-29' cannot use the index; instead compare the raw column: create_time = UNIX_TIMESTAMP('2014-05-29') .

12. Extend Existing Indexes Instead of Creating New Ones

If a table already has an index on column a and you need an index on (a,b), modify the existing index to include b rather than adding a separate index.

Note: The ultimate goal of index selection is to accelerate query speed. The principles above are basic guidelines; practitioners should adapt them to real‑world scenarios through continuous practice and analysis.