Master MySQL Performance: Architecture, Indexes, and Advanced Query Tuning

1. MySQL Basic Architecture

MySQL consists of a client layer (e.g., command‑line, Workbench, Navicat) and a server layer that splits into the SQL layer and the storage‑engine layer. After a query is executed, results are cached in the query cache before being sent back to the client.

2. Why SQL Optimization Is Needed

Complex queries such as multi‑table joins or sub‑queries can take minutes to run if the SQL is poorly written. Optimizing the statements reduces execution time and resource consumption.

2.1 Manual Optimization

Use EXPLAIN to view the execution plan, identify costly operations, and rewrite the query for better performance.

2.2 Automatic Optimizer

MySQL’s optimizer may rewrite a query automatically, but it can also override manual improvements, so understanding the plan is essential.

3. Index Fundamentals

An index is a data structure—typically a B+ tree in MySQL—that allows fast row location, similar to a book’s index.

3.1 Types of Indexes

Single‑column index

Unique index (no duplicate values)

Composite (multi‑column) index

3.2 Creating Indexes

Syntax:

CREATE INDEX index_name ON table_name(column_name);

Unique index:

CREATE UNIQUE INDEX idx_unique_name ON tb(name);

Composite index: CREATE INDEX idx_comp ON tb(dept, name); Alternative using ALTER TABLE:

ALTER TABLE tb ADD INDEX idx_dept (dept);

4. Analyzing Queries with EXPLAIN

The EXPLAIN output contains several important columns:

id : execution step identifier.

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

table : table used in this step.

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 part of the index.

ref : columns compared to the index.

rows : estimated rows examined.

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

4.1 Common Extra Flags

Using filesort

: MySQL must sort rows separately, often caused by ORDER BY on a column not covered by the index. Using temporary: A temporary table is created, typical for GROUP BY without a suitable index. Using index: Index‑only scan (covering index), no need to read the base table. Using where: Rows are filtered after index lookup, requiring a table lookup.

5. Practical Optimization Examples

Example 1 – Composite index order matters:

CREATE INDEX idx_a1_a2_a3 ON test02(a1,a2,a3);
EXPLAIN SELECT * FROM test02 WHERE a1='' ORDER BY a2;   -- no filesort
EXPLAIN SELECT * FROM test02 WHERE a1='' ORDER BY a3;   -- using filesort

Example 2 – Left‑join with small table driving large table:

CREATE INDEX idx_cid_teacher2 ON teacher2(cid);
EXPLAIN SELECT * FROM teacher2 t LEFT JOIN course2 c ON t.cid=c.cid WHERE c.cname='java';

Example 3 – Avoiding index loss with IN and range queries:

CREATE INDEX idx_typeid_authorid_bid ON book(typeid,authorid,bid);
EXPLAIN SELECT bid FROM book WHERE authorid=1 AND typeid IN(2,3) ORDER BY typeid DESC;  -- Using where because IN on the first column can invalidate the following columns.

6. Common Pitfalls and Best Practices

Keep the index prefix order consistent with query predicates (the “best left prefix”).

Never apply functions, type casts, or arithmetic on indexed columns; it disables the index.

Avoid !=, <>, IS NULL, or OR on indexed columns whenever possible.

Range conditions ( >, <, IN) can cause subsequent index columns to be ignored.

Use LIKE 'prefix%' instead of LIKE '%pattern%' to preserve index usage.

Prefer covering indexes ( Using index) to eliminate table lookups.

When ordering results, ensure ORDER BY columns are part of the index and follow the same sort direction.

By systematically analyzing EXPLAIN output, creating appropriate single‑column or composite indexes, and following the above guidelines, you can turn slow, multi‑second queries into fast, sub‑millisecond operations.