What Is Clustered Index And Non Clustered Index In MySQL?

Quick Fix Summary

If your MySQL queries crawl like a sloth at 7 a.m., the fastest win is usually “ALTER TABLE t ADD INDEX idx_name (col)” for a non-clustered index. If you’re still stuck, switch to a clustered index on the column you filter most—but remember: you can have only one. Run SHOW INDEX FROM t to see what’s already there.

What’s really going on when MySQL uses indexes?

Every MySQL table lives in one of two physical layouts. A clustered index physically sorts the rows on disk to match the index key—picture the table itself stored in B-tree order. In InnoDB (the default engine since 2010), the PRIMARY KEY doubles as the clustered index. No key? InnoDB quietly adds an invisible 6-byte row ID and uses that instead.

A non-clustered index is a separate B-tree that stores the indexed columns plus a “pointer” to the full row. If the table lacks a clustered index (a heap), that pointer is a physical row ID. If the table has a clustered index, the pointer becomes the clustered key value. Either way, MySQL has to hop from the index leaf to the clustered row, which adds extra I/O.

How to create and inspect indexes in MySQL 8.4 (2026)

See what’s already indexed
```
SHOW INDEX FROM employees;
```
Look at the “Key_name” and “Index_type” columns. You’re hunting for PRIMARY keys and any extra indexes.
Add a non-clustered index on the column you filter most
```
ALTER TABLE employees ADD INDEX idx_dept (department_id);
```
This builds a B-tree on department_id without rearranging the actual rows.
Turn a column into the clustered index (MySQL 8.4+)
```
ALTER TABLE employees ADD PRIMARY KEY (employee_id),
    ALGORITHM=INPLACE, LOCK=NONE;
```
Only one PRIMARY KEY is allowed; this rebuilds the table so rows sit in employee_id order.

Confirm the new index

SELECT INDEX_NAME, INDEX_TYPE, SEQ_IN_INDEX
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'employees';

Still not seeing the speed you expected?

Build a covering index When your query is SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id, tack the aggregate column onto the index so MySQL never leaves the index:
```
ALTER TABLE employees ADD INDEX idx_covering (department_id, employee_id);
SELECT dept_id, COUNT(*) FROM employees FORCE INDEX (idx_covering) GROUP BY dept_id;
```
Hide the index during big loads
```
ALTER TABLE huge_table ALTER INDEX idx_name INVISIBLE;
```
If inserts crawl, make the index invisible, run the load, then flip it back to visible.
Test with a MEMORY table Spin up a small MEMORY table with the same schema and run the same query:
```
CREATE TABLE test_emp ENGINE=MEMORY SELECT * FROM employees LIMIT 1000;
EXPLAIN SELECT * FROM test_emp WHERE dept_id = 5;
```
MEMORY uses hash indexes; if it’s fast, your bottleneck is disk I/O.

How can you avoid index problems before they start?

Index before you write the query Before you write a complex WHERE clause, run EXPLAIN and add indexes on every column that appears in a predicate. A missing index is the #1 reason queries slow down over time.
Keep indexes lean Use the smallest data type possible: INT instead of BIGINT, DATE instead of DATETIME. Every byte saved in the index cuts memory pressure and speeds up lookups.
Clean house every quarter Run this once a quarter to spot stale indexes:
```
SELECT table_name, index_name, stat_name, stat_value
FROM mysql.innodb_index_stats
WHERE stat_name = 'size';
```
Drop indexes that haven’t been read (stat_value = 0).
Cluster on the column you actually filter If 80 % of queries filter on customer_id, make that the PRIMARY KEY. You’ll trade a bit of write speed for massive read gains. Just remember: only one clustered index is allowed.

A quick reminder: MySQL 5.7 reached end-of-life in October 2023, so every example targets MySQL 8.4 (released 2024), which is fully supported in 2026.MySQL 8.4 Documentation

InnoDB became the default engine in MySQL 5.5 (2010) and still rules today.InnoDB as Default Storage Engine

If you’re staring at heap tables (MyISAM) in legacy databases, migrating to InnoDB usually speeds up queries by 3-5× thanks to clustered indexes.Migration to InnoDB

Contents

What’s really going on when MySQL uses indexes?

How to create and inspect indexes in MySQL 8.4 (2026)

Still not seeing the speed you expected?

How can you avoid index problems before they start?

Related Articles