MySQL - Non-Clustered Index

MySQL: Clustered and Secondary Indexes

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Instead of scanning an entire table row by row, the database can use an index to find the location of the desired data quickly. In MySQL, the InnoDB storage engine (the default) primarily uses two types of indexes: clustered and secondary.

Understanding the difference is key to designing high-performance databases.

The Clustered Index

In InnoDB, a table’s data is physically stored in the order of its clustered index. Think of it like a dictionary where the words (the data) are already sorted alphabetically (by the clustered index key). Because the data itself is the index, a table can only have one clustered index.

InnoDB automatically chooses the clustered index in this order of priority:

1. The table’s PRIMARY KEY.
2. If there is no PRIMARY KEY, the first UNIQUE index where all key columns are NOT NULL.
3. If neither of the above exists, InnoDB internally generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column containing row ID values.

Because of this, choosing a good primary key is one of the most important performance decisions you can make.

Secondary Indexes (Non-Clustered)

Any other index on an InnoDB table is a secondary index. Unlike the clustered index, a secondary index doesn’t contain the row data itself. Instead, it’s a separate data structure that contains the indexed column value(s) and a pointer back to the clustered index key (the primary key) for the corresponding row.

When you query using a secondary index, MySQL performs two lookups:

1. First, it searches the secondary index to find the primary key value for the matching row.
2. Second, it uses that primary key value to look up the full row data in the clustered index.

Syntax for Creating a Secondary Index

CREATE INDEX index_name ON table_name (column_name1, column_name2, ...);

Practical Example

Let’s create an employees table. The id column will be our primary key, and therefore, our clustered index. We will then add a secondary index on the department column to speed up searches by department.

-- Create the table. 'id' becomes the clustered index.
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50) NOT NULL,
    hire_date DATE NOT NULL
);

-- Create a secondary index to quickly find employees by department.
CREATE INDEX idx_department ON employees (department);

Verification

To see the indexes on a table, the SHOW INDEXES command is more informative than DESCRIBE.

SHOW INDEXES FROM employees;

The output will show both indexes:

Table	Non_unique	Key_name	Column_name
employees	0	PRIMARY	id
employees	1	idx_department	department

• PRIMARY: This is our clustered index. Non_unique is 0, meaning values must be unique.
• idx_department: This is our secondary index. Non_unique is 1, as multiple employees can be in the same department.

Indexing Best Practices

• Index for Searching: Add indexes to columns frequently used in WHERE clauses, JOIN conditions, and ORDER BY clauses.
• Choose a Good Primary Key: A short, unique, and non-changing primary key (like an INT AUTO_INCREMENT) is ideal for a clustered index.
• Composite Indexes: If you often filter by multiple columns together (e.g., WHERE department = 'Sales' AND hire_date > '2022-01-01'), create a composite index on both columns: CREATE INDEX idx_dept_hire_date ON employees (department, hire_date);. The order of columns matters!
• Don’t Over-Index: Every index consumes storage space and slows down write operations (INSERT, UPDATE, DELETE) because the indexes must also be updated. Only create indexes that provide a clear performance benefit.

Modern Indexing: Indexes on Expressions

Since MySQL 8.0.13, you can create indexes on expressions, not just columns. This is incredibly useful for queries that involve functions.

-- Imagine we often search for employees by last name in a case-insensitive way.
-- A regular index on 'last_name' wouldn't help a query like this:
-- SELECT * FROM employees WHERE LOWER(last_name) = 'chen';

-- We can create an index on the expression itself!
CREATE INDEX idx_lastname_lower ON employees ((LOWER(last_name)));

Now, the query will be able to use this functional index, making it much faster.

Analyzing Index Usage with EXPLAIN

The most practical way to see if an index is being used is the EXPLAIN command. It shows the execution plan for a query. Here is a practical example from an application perspective.

-- Let's add some data to our employees table first.
INSERT INTO employees (first_name, last_name, department, hire_date) VALUES
('Ayla', 'Chen', 'Engineering', '2021-06-15'),
('Ben', 'Carter', 'Sales', '2020-02-01');

-- Now, let's analyze a query. We are searching on a non-indexed column.
EXPLAIN SELECT * FROM employees WHERE last_name = 'Chen';

-- Output will show a 'type' of 'ALL', meaning a full table scan.

-- Let's add an index.
CREATE INDEX idx_lastname ON employees (last_name);

-- Run the analysis again.
EXPLAIN SELECT * FROM employees WHERE last_name = 'Chen';

-- Now the output will show a 'type' of 'ref' and 'key' as 'idx_lastname',
-- indicating the index was used for a fast lookup.

In a client program, you could run EXPLAIN on your application’s queries to diagnose performance issues and decide where new indexes are needed. This is a fundamental skill for any developer working with databases.