Comprehensive Guide to MySQL SQL Optimization and Index Usage

MySQL Basic Architecture

Describes the client and server components, the SQL layer, the storage‑engine layer, query cache, and the execution flow of MySQL queries.

SQL Optimization

Explains why optimization is required, outlines the query compilation and execution process, and emphasizes that index optimization is the most effective way to improve performance.

Indexes

Defines an index as a data structure (typically a B+ tree) that speeds up data retrieval, and introduces three main index types: single‑value, unique, and composite.

Shows how to create a table with a specific storage engine:

create table tb(
    id int(4) auto_increment,
    name varchar(5),
    dept varchar(5),
    primary key(id)
) engine=myISAM auto_increment=1 default charset=utf8;

Examples of creating indexes using both CREATE INDEX and ALTER TABLE syntax:

-- Single‑value index
create index dept_index on tb(dept);

-- Unique index (assumes the column values are unique)
create unique index name_index on tb(name);

-- Composite index
create index dept_name_index on tb(dept, name);

-- Alternate syntax with ALTER TABLE
alter table tb add index dept_index(dept);
alter table tb add unique index name_index(name);
alter table tb add index dept_name_index(dept, name);

EXPLAIN Plan Keywords

Details the meaning of the most important columns returned by EXPLAIN: id, select_type, type, possible_keys, key, key_len, rows, and Extra. Shows how each keyword influences the optimizer’s choice.

Common Optimization Scenarios

Provides practical advice such as:

Keep the index order consistent with the query order (best‑left‑prefix).

Avoid functions, type casts, or arithmetic on indexed columns.

Place IN clauses at the end of the WHERE condition to prevent index loss.

Use patterns like LIKE 'prefix%' instead of LIKE '%suffix' to retain index usage.

Prefer covering indexes ( using index) to avoid reading the base table.

Avoid OR conditions that can invalidate multiple indexes.

Multi‑Table Optimization

Explains the principle of “small table drives large table” for joins, recommends adding indexes on join columns, and shows examples of left‑join optimization.

Example of adding an index on the join column of the left table: create index cid_teacher2 on teacher2(cid); After adding the index, the EXPLAIN output shows the join using the index instead of a full table scan.

Best‑Practice Checklist

Design indexes that match the query’s filter and sort order.

Prefer single‑column indexes for highly selective columns.

Use composite indexes when multiple columns are filtered together, respecting the left‑most prefix rule.

Remove unused or redundant indexes to avoid optimizer confusion.

Regularly review EXPLAIN output to verify that the intended indexes are actually used.

Following these guidelines helps achieve lower I/O, reduced CPU usage, and faster query response times in MySQL environments.