MySQL: Introduction to MySQL Queries and the EXPLAIN…
Last updated: 2026-08-26
You can only write truly high-performance queries if you understand the advanced features of indexes.
This lesson provides an in-depth explanation of index types and EXPLAIN analysis.
1. What You'll Learn
- Clustered Index vs. Nonclustered Index
- Covering Index
- Composite Indexes and the Leftmost Prefix
- Scenarios in Which Indexes Become Inactive
- Interpreting the EXPLAIN Execution Plan
2. Clustered Indexes and Nonclustered Indexes
| Dimension | Clustered Index | Nonclustered Index |
|---|---|---|
| Storage Method | Data and indexes are stored together | Indexes point to data addresses |
| Quantity | Only one allowed | Multiple allowed |
| Primary Key | InnoDB automatically uses the primary key | Secondary Index |
| Query | Retrieve data directly | Requires a table lookup |
(1) Lookup in a table
graph LR
A[Secondary Index<br/>idx_email] -->|Primary Key Value| B[Clustered Index<br/>PRIMARY]
B -->|Complete Data| C[Data Row]
SQL
-- The process of table lookup
SELECT * FROM users WHERE email = 'alice@email.com';
-- 1. Find id=1 in idx_email
-- 2. Find complete data for id=1 in PRIMARY
3. Covering Indexes
All the fields in the query are included in the index, so there is no need to look up the table.
▶ Example: Overlay Index
SQL
-- Create a composite index
CREATE INDEX idx_name_email ON users(username, email);
-- Covering Index（No need to return to the table）
SELECT username, email FROM users WHERE username = 'alice';
-- Non-covering index（Need to return to the table）
SELECT * FROM users WHERE username = 'alice';
Output:
TEXT
📖 Display only
Output displayed
| Type | Returns to Table | Performance |
|---|---|---|
| Covering Index | No | Fast |
| Non-covering index | Yes | Slow |
4. Composite Indexes and the Leftmost Prefix
(1) The Leftmost Prefix Principle
The composite index (a, b, c) can be used in the following queries:
SQL
WHERE a = 1 -- ✅ Using index
WHERE a = 1 AND b = 2 -- ✅ Using index
WHERE a = 1 AND b = 2 AND c = 3 -- ✅ Using index
WHERE b = 2 -- ❌ Index not used
WHERE b = 2 AND c = 3 -- ❌ Index not used
▶ Example: Verifying Index Usage
SQL
CREATE INDEX idx_abc ON orders(customer_id, status, order_date);
-- ✅ Using index
EXPLAIN SELECT * FROM orders WHERE customer_id = 1;
EXPLAIN SELECT * FROM orders WHERE customer_id = 1 AND status = 'paid';
EXPLAIN SELECT * FROM orders WHERE customer_id = 1 AND status = 'paid' AND order_date > '2026-01-01';
-- ❌ Index not used (Violates the Leftmost Prefix Rule)
EXPLAIN SELECT * FROM orders WHERE status = 'paid';
Output:
TEXT
📖 Display only
Output displayed
5. Scenarios Where Indexes Become Ineffective
| Scenario | Example | Reason |
|---|---|---|
| Function Operations | WHERE YEAR(date) = 2026 |
Index Enclosed by a Function |
| Implicit Conversion | WHERE phone = 13800001111 |
Querying String Arrays Using Numbers |
| LIKE Left Fuzzy | WHERE name LIKE '%john' |
Prefix Uncertain |
| OR Condition | WHERE a = 1 OR b = 2 |
Some fields are not indexed |
| NOT IN/NOT EXISTS | WHERE id NOT IN (1,2) |
Optimizer selects a full table scan |
| IS NULL/IS NOT NULL | WHERE col IS NULL |
Depends on NULL proportion |
▶ Example: Index Failure and Recovery
SQL
-- ❌ Index Invalidation
SELECT * FROM users WHERE YEAR(created_at) = 2026;
-- ✅ Fix: Change to a range query
SELECT * FROM users WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
-- ❌ Index Invalidation
SELECT * FROM users WHERE phone = 13800001111;
-- ✅ Fix: Use quotes
SELECT * FROM users WHERE phone = '13800001111';
Output:
TEXT
📖 Display only
Output displayed
6. EXPLAIN Execution Plan
▶ Example: Using EXPLAIN
SQL
EXPLAIN SELECT * FROM users WHERE email = 'alice@email.com';
Output:
TEXT
📖 Display only
+----+-------+------+---------+------+----------+-------+
| id | type | key | key_len | ref | rows | Extra |
+----+-------+------+---------+------+----------+-------+
| 1 | const | idx_email | 402 | const | 1 | |
+----+-------+------+---------+------+----------+-------+
(1) Key Field Descriptions
| Field | Description | Valid Values |
|---|---|---|
| type | Access type | const > eq_ref > ref > range > index > ALL |
| key | Index Used | Not NULL |
| rows | Estimated number of rows to scan | The lower, the better |
| Extra | Additional Information | Using Index (Covering Index) |
(2) type Access Type
| Type | Description | Pros and Cons |
|---|---|---|
| const | Primary key/unique index equality queries | ⭐⭐⭐ |
| eq_ref | Use primary key/unique index for joins | ⭐⭐⭐ |
| ref | Equality queries on non-unique indexes | ⭐⭐ |
| range | Index Range Query | ⭐⭐ |
| index | Full index scan | ⭐ |
| ALL | Full Table Scan | ❌ |
❓ FAQ
Q How should I choose the field order for a composite index?
A Place fields with high selectivity first, and fields with high query frequency first.
Q Are the "rows" values in EXPLAIN accurate?
A They are estimates, not exact values. However, they can be used to assess query efficiency.
Q What should I do if an index becomes ineffective?
A Rewrite the SQL to avoid situations where the index becomes ineffective, or create a function index (MySQL 8.0+).
Q Are the "rows" values in EXPLAIN accurate?
A They are estimates, not exact values, but you can assess the effectiveness of optimizations by comparing relative changes. Use ANALYZE TABLE to update the statistics.
Q When should you use a prefix index?
A When a VARCHAR field is very long and the prefix is sufficiently distinctive. For example,
INDEX(email(10))—as long as the first 10 characters have a distinctiveness of >95%.📖 Summary
- Clustered indexes store data and indexes together, while nonclustered indexes require a table lookup.
- Covering Index: All query fields are included in the index, so there is no need to look up the table.
- Leftmost prefix: A composite index matches from left to right
- Index Invalidation scenarios: Functions, implicit conversions, left fuzzy, OR
- EXPLAIN Analyze the query plan, focusing on type/key/rows
📝 Exercises
-
Basic Question (Difficulty: ⭐): Use
EXPLAINto analyze a query and determine whether an index is being used. -
Advanced Problem (Difficulty ⭐⭐): Create a composite index to verify the leftmost prefix principle.
-
Challenge Question (Difficulty: ⭐⭐⭐): Identify three scenarios where indexes are ineffective and propose solutions.
