Master MySQL Optimization: Indexes, Explain Plans, and Performance Tips

MySQL Basic Architecture

MySQL consists of a client layer (e.g., CMD, WorkBench, Navicat) and a server layer that can be divided into the SQL layer and the storage engine layer. After a query is executed, the result is first cached in the query cache and then returned to the client.

Show engines command:

Show variables like "%storage_engine%" command:

Creating a Table Example

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;

SQL Optimization

Why optimize SQL? Complex multi‑table joins or sub‑queries can cause execution times of hours, so learning optimization techniques is essential.

SQL Writing and Parsing Process

Writing process (simplified):

select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...

Parsing process (simplified):

from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...

Index Basics

An index is a data structure (usually a B+ tree) that speeds up data retrieval. The B+ tree stores all data in leaf nodes, while internal nodes store pointers for navigation.

When a query uses an index, MySQL can locate the required rows directly, reducing the number of row scans dramatically (e.g., finding age=33 in 3 scans instead of scanning 5 rows).

Index Advantages and Drawbacks

Advantages: faster queries, lower I/O, reduced CPU usage for ordered queries.

Drawbacks: extra storage, slower INSERT/UPDATE/DELETE, not useful for small tables, frequently updated columns, or rarely used columns.

Index Types and Creation

Single‑value index (one column): create index dept_index on tb(dept); Unique index (enforces uniqueness): create unique index name_index on tb(name); Composite index (multiple columns):

create index dept_name_index on tb(dept, name);

Index Deletion and Query

drop index name_index on tb;

To view an index:

show index from tb;

EXPLAIN Output Keywords

Key columns in the EXPLAIN result:

id : execution step identifier.

select_type : type of SELECT (simple, primary, subquery, derived).

type : join type (system, const, eq_ref, ref, range, index, ALL).

possible_keys : indexes that could be used.

key : index actually used.

key_len : length of the used index (helps determine if a composite index is fully used).

rows : estimated rows examined.

extra : additional info (using filesort, using temporary, using index, using where, impossible where, etc.).

Examples:

explain select * from teacher where tcid>=4;

Using Filesort

Occurs when ORDER BY uses a column not covered by the index. Example:

explain select * from test02 where a1='' order by a2;

Using Temporary

Appears with GROUP BY that cannot be satisfied by an index.

explain select a1 from test02 where a1 in ('1','2','3') group by a2;

Using Index (Covering Index)

If all columns in the SELECT are in the index, MySQL can retrieve data without accessing the table.

explain select a1,a2 from test02 where a1='' or a2='';

Using Where

Indicates that after using an index, MySQL still needs to read the table (row‑by‑row) for additional filtering.

explain select a1,a3 from test02 where a1='' and a3='';

Optimization Examples

Single‑Table Optimization

Querying a table with conditions on indexed columns and ordering by the same column avoids filesort.

explain select * from test02 where a1='' order by a1;

Two‑Table Join Optimization

Use the smaller table to drive the larger one and add indexes on the join columns.

create index cid_teacher2 on teacher2(cid);
explain select * from teacher2 t left join course2 c on t.cid=c.cid where c.cname='java';

Three‑Table Join Optimization

Same principle: small table drives large tables, and indexes are placed on frequently used columns.

Common Pitfalls that Invalidate Indexes

Applying functions or calculations on indexed columns (e.g., authorid*2=1).

Range queries ( IN, >, <) that appear before other indexed columns in a composite index.

Using !=, <>, or IS NULL / IS NOT NULL on indexed columns.

Using OR between conditions on indexed columns – often forces a full scan.

LIKE patterns that start with a wildcard (e.g., LIKE "%x%") – index cannot be used.

Implicit or explicit type conversion on indexed columns.

Best Practices Summary

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

Prefer composite indexes that cover all columns used in WHERE, ORDER BY, and SELECT.

Remove obsolete indexes to avoid optimizer confusion.

Place range or IN conditions at the end of the index key.

Use covering indexes (using index) whenever possible.

Avoid functions, OR, NOT, and leading wildcards on indexed columns.

For joins, let the smaller table drive the larger one and index the join columns.

By following these guidelines and regularly reviewing EXPLAIN output, you can significantly improve MySQL query performance.