Master MySQL Performance: Practical SQL Optimization and Indexing Techniques

MySQL Basic Architecture

MySQL consists of a client layer (e.g., command‑line, Workbench, Navicat) and a server layer that separates the SQL processing layer from the storage engine layer. After a query is executed, results are cached in the query cache for fast reuse and then sent back to the client.

Index Fundamentals

An index is a data structure (typically a B+ tree) that allows MySQL to locate rows quickly, similar to a dictionary’s table of contents. Creating an index on a column reduces the number of row scans needed to satisfy a query.

Key concepts:

Single‑column index

Unique index (no duplicate values)

Composite (multi‑column) index

Creating Indexes

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;

create index dept_index on tb(dept);

create unique index name_index on tb(name);

create index dept_name_index on tb(dept, name);

Altering Indexes

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);

Dropping Indexes

drop index name_index on tb;

SQL Optimization Overview

Complex queries involving joins, subqueries, or poor‑written SQL can cause long execution times. Optimizing mainly focuses on improving index usage.

Explain Plan Keywords

The EXPLAIN statement reveals how MySQL will execute a query. Important columns include:

id : execution step identifier

select_type : query type (simple, primary, subquery, derived, union)

table : table name

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

rows : estimated rows examined

Extra : additional info (e.g., using filesort, using temporary, using index)

Common Extra Values

using filesort : MySQL must sort rows separately, often because ORDER BY uses a different column than the index.

using temporary : a temporary table is created, typically for GROUP BY.

using index : index covering; MySQL can satisfy the query using only the index without reading the base table.

using where : index used for part of the query, but MySQL must read the base table for other columns.

Index Management Examples

Creating and testing indexes on sample tables demonstrates how different index types affect the EXPLAIN output.

# Create table test_kl
create table test_kl (
    name char(20) not null default ''
);
alter table test_kl add index index_name(name);
explain select * from test_kl where name='';

The output shows key_len = 20, indicating the full use of the name index.

Practical Optimization Cases

Several real‑world scenarios illustrate how to rewrite queries and adjust indexes for better performance.

Case 1: Composite Index Order

create index a1_a2_a3 on test03(a1, a2, a3);
explain select a3 from test03 where a1=1 and a2=2 and a3=3;

When the WHERE clause follows the index order, MySQL can use the index efficiently. Reversing the order forces the optimizer to rearrange the query.

Case 2: In‑Clause Placement

Placing an IN condition at the end of the WHERE clause can prevent index loss.

create index authorid_typeid_bid on book(authorid, typeid, bid);
explain select bid from book where authorid=1 and typeid in (2,3) order by typeid desc;

Even though the index is present, the IN range may cause typeid to be ignored, resulting in using where.

Case 3: Join Optimization

For a left join, index the foreign key on the smaller (driving) table.

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';

Adding an index on cname further improves the plan.

Best Practices and Pitfalls

Always keep the index definition order consistent with query usage (best‑left‑prefix).

Avoid functions, type casts, or arithmetic on indexed columns; they invalidate the index.

Do not use !=, <>, or IS NULL on indexed columns in composite indexes, as they cause the index and all columns to the right to be ignored.

Range operators ( >, <, IN) can render subsequent index columns ineffective.

Prefer LIKE 'prefix%' over LIKE '%pattern%' to allow index usage.

Use EXISTS for subqueries that return many rows and IN when the subquery result set is small.

When ordering, align ORDER BY columns with the index order to avoid using filesort.

By following these guidelines and iteratively examining EXPLAIN output, developers can achieve significant performance gains in MySQL workloads.