SQL - Non-Clustered Index

SQL Indexes: The Non-Clustered Index

What is a Database Index?

A database index is a data structure that improves the speed of data retrieval operations on a database table. Think of it like the index at the back of a book. Instead of reading the entire book (a full table scan) to find a topic, you look up the topic in the index, which tells you the exact page number (the data’s location) to turn to. This is much faster.

Clustered vs. Non-Clustered Indexes

Databases typically have two main types of indexes, and their key difference is how they store data.

• Clustered Index: This index determines the physical order of data in a table. Because the data can only be sorted in one physical way, you can only have one clustered index per table. In many databases (like MySQL’s InnoDB and SQL Server), the primary key is automatically the clustered index.
• Non-Clustered Index: This index has a structure separate from the data rows. It contains the indexed column values and a pointer (a ‘row locator’) to the corresponding data row. Because it’s a separate structure, you can have multiple non-clustered indexes on a single table.

How Non-Clustered Indexes Work

When you create a non-clustered index on a column (e.g., City), the database creates a new, sorted list of all city values. Each entry in this list has a pointer back to the full data row in the main table.

When you run SELECT * FROM Customers WHERE City = 'Mumbai', the database can:

1. Quickly search the sorted non-clustered index for ‘Mumbai’.
2. Find the pointer(s) associated with ‘Mumbai’.
3. Use the pointer(s) to go directly to the data row(s) in the main table and retrieve them.

In MySQL’s InnoDB engine, all indexes other than the primary key are secondary indexes, which are functionally non-clustered. The ‘pointer’ in a secondary index is actually the primary key value of the row. The database then uses that primary key to look up the row in the clustered index.

Standard Syntax

The standard SQL syntax for creating an index (which is typically non-clustered by default) is straightforward.

CREATE INDEX index_name
ON table_name (column_name);

Example: Indexing a Customers table

Let’s use a Customers table. Queries that filter by City would be slow on a large, un-indexed table.

CREATE TABLE Customers (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    Age INT NOT NULL,
    City VARCHAR(100),
    Email VARCHAR(100) UNIQUE
);

-- Inserting data...
INSERT INTO Customers (Name, Age, City, Email) VALUES
    ('Muffy', 24, 'Indore', 'muffy@example.com'),
    ('Ramesh', 32, 'Ahmedabad', 'ramesh@example.com'),
    ('Komal', 22, 'Hyderabad', 'komal@example.com'),
    ('Khilan', 25, 'Delhi', 'khilan@example.com'),
    ('Chaitali', 25, 'Mumbai', 'chaitali@example.com');

Let’s create a non-clustered index on the City column to speed up lookups.

-- Creating an index to speed up searches by city
CREATE INDEX idx_customers_city ON Customers(City);

Executing this command doesn’t change the data you see with SELECT *. The table’s physical order is unchanged. It only creates the separate index structure in the background.

Composite (Multi-Column) Indexes

You can also create an index on multiple columns. This is useful for queries that filter on several columns simultaneously.

-- This index is optimized for queries filtering by City AND Age
CREATE INDEX idx_customers_city_age ON Customers(City, Age);

The order of columns matters! An index on (City, Age) is most effective for queries with WHERE City = '...' AND Age = '...' or just WHERE City = '...'. It is less effective for a query that only has WHERE Age = '...'.

How to Verify an Index is Used

Simply creating an index doesn’t guarantee the database will use it. The query optimizer makes that decision. To check, you can use the EXPLAIN (or EXPLAIN ANALYZE) command, which shows the execution plan for a query.

EXPLAIN SELECT ID, Name FROM Customers WHERE City = 'Mumbai';

The output of EXPLAIN is database-specific, but you should look for information indicating that your index (e.g., idx_customers_city) is being used. If you see a ‘Full Table Scan’, it means the database is reading the entire table, and the index was not used for that query.

The Trade-offs of Indexing

Indexes are not free. They come with trade-offs you must consider.

Advantages

• Faster Reads: Significantly speeds up SELECT queries with WHERE, JOIN, and ORDER BY clauses on indexed columns.

Disadvantages

• Slower Writes: Every time you INSERT, UPDATE, or DELETE a row, the database must also update all the indexes on that table. Too many indexes can slow down write operations.
• Storage Space: Indexes take up disk space. For large tables, indexes can be substantial.
• Maintenance Overhead: The database needs to maintain the index structure, which adds a small amount of overhead.

The key is to be strategic: index columns that are frequently used for searching and joining, but avoid over-indexing a table, especially one with heavy write traffic.