MySQL Index Types, Usage Guidelines, and EXPLAIN Interpretation

1. Types of Indexes

MySQL indexes include ordinary, unique, primary key, composite, and full‑text indexes. Indexes do not contain columns with NULL values; index entries can be NULL, but rows with NULL are excluded from the index.

Ordinary index: create index index_name on table(column); or specify during table creation create table(..., index index_name column); Unique index: similar to ordinary but column values must be unique (can be NULL). create unique index index_name on table(column); or unique index_name column in CREATE TABLE.

Primary key index: a special unique index that cannot be NULL; only one per table, defined with primary key(column) in CREATE TABLE.

Composite index: index on multiple columns following the leftmost prefix rule. Example: alter table t add index index_name(a,b,c); Full‑text index: used for keyword search in text, works with MATCH...AGAINST, can be created on CHAR, VARCHAR, TEXT columns.

2. When to Use Indexes

MySQL uses only one index per query; combining indexes can be more costly than a full table scan, so a composite index on (A,B) is optimal for queries like WHERE A=a AND B=b.

Primary key or unique columns

Columns used in joins

Columns used with >, >=, =, <, <=, IS NULL, BETWEEN in WHERE

LIKE patterns that do not start with a wildcard, e.g., WHERE A LIKE 'China%' Fields used in MIN() or MAX()

Columns in ORDER BY or GROUP BY

3. When Not to Use Indexes

Very small tables

Columns with low cardinality and uniform distribution

Tables with frequent INSERT/UPDATE/DELETE operations

Large TEXT, IMAGE columns (except possibly indexing the first N characters)

When MySQL estimates a full table scan is faster

4. When Indexes Become Ineffective

Composite index not using leftmost prefix (e.g., WHERE B=b on index (A,B))

LIKE pattern not using leftmost prefix (e.g., WHERE A LIKE '%China')

Using one index for filtering and another for ORDER BY; only the filtering index is used

OR conditions can disable index usage unless on the same column

String literals must be quoted; otherwise type conversion disables index

Functions, OR, !=, NOT IN on indexed columns prevent index usage

5. EXPLAIN Statement Types

EXPLAIN output shows the type column indicating how MySQL accesses rows:

ALL: full table scan

index: full index scan

range: index range scan (used with <, <=, >=, BETWEEN, etc.)

ref: non‑unique index scan or unique index prefix scan, returns matching rows, common in joins

eq_ref: similar to ref but uses a unique index (primary key) for a single row

const/system: single row, treated as constant

null: MySQL accesses no table or index, returns result directly

The key column shows which index is used; null means none.