Understanding MySQL Index Usage and the Leftmost Prefix Principle

The article introduces the importance of SQL performance and the frequent neglect of index optimization.

It creates a sample table student with a primary key, a composite index on sname, s_code, address, and a regular index on height, and inserts sample data.

It explains the leftmost prefix principle: a composite index can be used only if the query conditions start with the leftmost column(s) of the index, and range conditions stop further matching.

Examples are given showing which of several SELECT statements on student will use the index and which will not, with

EXPLAIN SELECT create_time FROM student WHERE sname = "变成派大星"

results illustrating full‑table scans versus index scans.

The article discusses why SELECT * does not inherently break indexes, but can cause extra back‑table lookups, and why range queries may or may not use indexes depending on result set size.

It covers function usage, showing that applying functions to indexed columns prevents index usage unless a functional index is defined (available from MySQL 8.0).

Additional pitfalls are described: leading wildcards in LIKE patterns, misuse of OR with non‑indexed columns, large IN lists, and ORDER BY that forces a full scan when the index order does not match the sort.

Finally, the article summarizes key take‑aways: indexes are used when the leftmost columns are matched with equality, range queries need small result sets, avoid unnecessary columns in SELECT, and design indexes carefully to match query patterns.