How to check if index is being used mysql

MysqlHow-ToBeginner · 3 min read

How to Check if Index is Being Used in MySQL

Use the EXPLAIN statement before your query to see if MySQL uses an index. The output shows a key column indicating the index used; if it is NULL, no index is used.

📐

Syntax

The EXPLAIN statement is used before a SELECT query to show how MySQL executes it. Key parts of the output include:

key: The index MySQL chooses to use.
possible_keys: Indexes that could be used.
rows: Number of rows MySQL estimates to examine.

sql

EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';

💻

Example

This example shows how to check if an index is used on the users table when searching by email.

sql

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  email VARCHAR(100),
  INDEX idx_email (email)
);

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

Output

id: 1 select_type: SIMPLE table: users partitions: NULL type: ref possible_keys: idx_email key: idx_email key_len: 102 ref: const rows: 1 filtered: 100.00 Extra: Using index condition

⚠️

Common Pitfalls

Sometimes MySQL does not use an index even if it exists. Common reasons include:

The query condition is not selective enough.
The index is not suitable for the query.
Functions or operations on the indexed column prevent index use.
Small tables where a full scan is faster.

Always check the key column in EXPLAIN output to confirm index usage.

sql

/* Wrong: Using a function on indexed column disables index */
EXPLAIN SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

/* Right: Use direct comparison to allow index use */
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

📊

Quick Reference

Remember these tips when checking index usage:

Use EXPLAIN before your SELECT query.
Look at the key column to see the index used.
If key is NULL, no index is used.
Check possible_keys to see candidate indexes.
Optimize queries to allow index usage (avoid functions on indexed columns).

✅

Key Takeaways

Use EXPLAIN before your SELECT query to check index usage.

The key column in EXPLAIN output shows which index is used or NULL if none.

Avoid functions on indexed columns to ensure indexes can be used.

Check possible_keys to see which indexes MySQL could use.

Small tables or low-selectivity queries may not use indexes.