Understanding Database Indexes: Benefits, Usage Principles, and Analysis with EXPLAIN

Indexes can dramatically improve data retrieval speed, accelerate query execution, and reduce CPU consumption during sorting and grouping, but they also increase write‑time I/O, consume storage space, and may slow updates.

Usage principles include limiting the number of indexes to no more than 20% of fields (or five indexes for tables with frequent writes), avoiding indexes on low‑cardinality columns (e.g., gender), not creating indexes on very small tables (under 100 rows), using unique indexes for columns with unique values, eliminating duplicate functional indexes, ordering composite index columns by most selective fields, and keeping the number of columns in a composite index to three or fewer.

Analyzing indexes with EXPLAIN

Adding EXPLAIN before a SELECT statement shows how MySQL will use indexes. The output columns include:

id : query identifier and execution order.

table : the table being accessed.

type : join type, ranked from best to worst as system > const > eq_ref > ref > range > index > ALL .

possible_keys : indexes that could be used.

key : the actual index chosen (NULL if none).

key_len : length of the used index.

ref : column(s) used from the index.

rows : estimated rows examined.

Extra : additional information such as Using filesort , Using temporary , Using where , etc.

Examples of type values:

system : single‑row table.

const : at most one matching row, read once.

eq_ref : one row per join using a primary or unique key.

ref : non‑unique index or leftmost prefix.

range : index range scan (e.g., > , < ).

index : full index scan.

ALL : full table scan (to be avoided).

Common causes of index loss include:

Using LIKE patterns that start with a wildcard (e.g., %aaa% ) which prevents index use, while patterns like aaa% can use an index.

Applying functions or arithmetic operators to indexed columns, which disables the index.

When the query accesses more than 30% of the table rows, a full scan may be chosen.

Storing numeric values as strings and omitting quotes in WHERE clauses.

Illustrative screenshots of query plans and index usage are included in the original article.