Master MySQL Indexes: Syntax, Types, and Optimization Strategies

1. MySQL Index Syntax

Indexes can be defined when creating a table or added later. Example of creating a table with an index:

CREATE TABLE mytable(
    ID INT NOT NULL,
    username VARCHAR(16) NOT NULL,
    INDEX index_name (username(length))
);

Adding an index to an existing table:

ALTER TABLE my_table ADD [UNIQUE] INDEX index_name(column_name);
-- or
CREATE INDEX index_name ON my_table(column_name);

Dropping an index:

DROP INDEX my_index ON tablename;
-- or
ALTER TABLE table_name DROP INDEX index_name;

Viewing indexes and their usage:

SHOW INDEX FROM tablename;
EXPLAIN SELECT * FROM table_name WHERE column_1='123';

2. Advantages and Disadvantages of Indexes

Advantages: Faster data retrieval, reduced I/O, and accelerated grouping/sorting when queries use indexed columns.

Disadvantages: Indexes consume disk space (often ~1.5× the size of the data table) and require maintenance time; they also slow down INSERT/UPDATE/DELETE operations because the index must be updated.

3. Types of Indexes

Primary index (unique, no NULLs)

Unique index (unique values, NULLs allowed)

Normal index (no restrictions)

Full‑text index (for large text columns)

Composite index (multiple columns; follows the left‑most prefix rule)

Examples:

ALTER TABLE table_name ADD PRIMARY KEY pk_index(col);
ALTER TABLE table_name ADD UNIQUE uniq_idx(col);
ALTER TABLE table_name ADD INDEX idx(col);
ALTER TABLE table_name ADD FULLTEXT ft_idx(col);
ALTER TABLE table_name ADD INDEX comp_idx(col1, col2, col3);
-- Short index example
ALTER TABLE table_name ADD INDEX short_idx(col1(4), col2(3));

4. Implementation Principles

MySQL supports multiple storage engines, each offering different index implementations:

Hash index: Only the MEMORY engine supports it; fast exact‑match lookups but cannot be used for range scans or sorting.

Full‑text index: Available in MyISAM (pre‑5.6) and InnoDB (5.6+). It builds a word list for text columns and uses MATCH ... AGAINST queries instead of LIKE.

B‑Tree and B+Tree indexes: Used by most engines. B‑Tree stores keys and data in leaf nodes; B+Tree stores only keys in internal nodes and all data in leaf nodes, reducing disk I/O.

B+Tree structure (illustrated below) enables efficient binary search with low tree height (typically around 3).

InnoDB uses clustered indexes (primary key data stored together with the index) while MyISAM uses non‑clustered indexes (index and data stored separately).

5. Index Usage Strategies

When to use indexes:

Primary key (automatically unique)

Columns frequently appearing in WHERE or ORDER BY Foreign‑key columns

High‑concurrency scenarios – consider composite indexes

Columns used in aggregate functions (e.g., MAX(col), COUNT(col))

When NOT to use indexes:

Columns with frequent INSERT/UPDATE/DELETE

Columns with many duplicate values

Very small tables (less than ~1,000 rows) where full scans are cheap

Common cases of index loss:

NULL values in a composite index column

Using an index more than once in a single query (e.g., WHERE uses it, then ORDER BY tries to reuse it)

Leading wildcard in LIKE (e.g., '%abc%')

Functions or expressions on indexed columns (e.g., YEAR(date))

Range or inequality operators that prevent index use, except for primary key equality

Missing quotes causing type mismatch

OR conditions without individual indexes (use UNION ALL instead)

Sorting on a column not included in the SELECT list (except when sorting by primary key)

6. Index Optimization Techniques

Left‑most prefix: For a composite index (col1, col2, col3), queries can use the index only if they filter on col1, (col1, col2), or (col1, col2, col3).

Short indexes: Define a prefix length for long character columns to save space and improve speed.

Full‑text for fuzzy searches: Use MATCH ... AGAINST instead of LIKE '%term%' when searching large text.

Composite indexes for high‑concurrency workloads.

Reference: https://www.jianshu.com/p/c82148473235