Master MySQL Optimization: Indexing, Locking, Pagination & Profiling Tips

MySQL is widely known, yet many developers struggle to use it efficiently; this guide reviews common interview‑level SQL optimization tactics.

1. Create Indexes

Always create appropriate indexes; without them, queries perform full table scans and become slow.

Answer: No, you should not create indexes for every possible WHERE condition.

MySQL uses B+‑tree structures (space for time) and updates the index tree on data changes, affecting performance.

Following the 80/20 principle, limit single‑table indexes to five and composite indexes to no more than five columns.

Do not create an index on every column.

Each InnoDB table must have a primary key.

Pay attention to the order of columns in composite indexes.

Prefer covering indexes.

Avoid foreign‑key constraints when possible.

2. Avoid Index Invalidity

Having an index does not guarantee optimal performance; index loss can cause slow queries.

Common scenarios where indexes become ineffective:

LIKE queries starting with %.

Composite indexes not satisfying the left‑most prefix rule (e.g., index on (type, status, uid) but query uses status and uid only).

OR conditions where at least one operand lacks an index.

Functions or operations on indexed columns (e.g., UPPER(), !=, NOT IN, etc.).

3. Lock Granularity

MySQL storage engines: MyISAM uses table locks; InnoDB supports row and table locks.

For update operations, row locks reduce lock conflicts and wait times, so InnoDB is generally preferred.

4. Pagination Query Optimization

Standard LIMIT with large offsets scans many rows before discarding them, leading to poor performance.

<code>select * from table limit #{start}, #{pageSize};</code>

Improved method: retrieve the maximum id from the previous page and query using a range condition.

<code>select * from table where id > #{id} limit #{pageSize};</code>

This approach supports only previous/next page navigation, not arbitrary jumps.

5. Avoid SELECT *

SELECT * forces the engine to read the primary key from the secondary index and then perform a back‑table lookup, which is costly.

Specify required columns and ensure they are covered by an index to eliminate the back‑lookup.

<code>select * from table where buyer_id = #{buyer_id};</code>

6. EXPLAIN Analyze Execution Plan

Understanding EXPLAIN output helps avoid performance pitfalls. Key fields include:

id – execution order.

select_type – query type (SIMPLE, PRIMARY, UNION, SUBQUERY, etc.).

type – access method (system, const, eq_ref, ref, range, index, ALL).

possible_keys – indexes that could be used.

key – index actually used.

rows – number of rows examined.

Extra – additional information.

<code>explain select * from user where id < 20;</code>

7. Show Profile for Performance

SHOW PROFILE provides a detailed runtime breakdown, complementing EXPLAIN.

Enable profiling:

<code>set profiling=1;</code>

View collected profiles:

<code>show profiles;</code>

After obtaining a Query_ID, retrieve detailed timing:

<code>show profile for query <Query_ID>;</code>