Indexes | SQL Tutorial | TechCoder.io

What is an Index?

An index is a database structure that speeds up data retrieval operations on a table at the cost of additional writes and storage space. Think of it like the index at the back of a book.

How Indexes Work (B-Tree)

Most databases use B-Tree indexes.

Without Index: Full table scan (checking every row). Complexity: O(N).
With Index: Binary search traversal. Complexity: O(log N).

Creating an Index

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Example

Create an index on the LastName column of the Employees table:

CREATE INDEX idx_lastname
ON Employees (LastName);

Unique Index

Ensures no duplicate values in the indexed column(s).

CREATE UNIQUE INDEX idx_email
ON Users (Email);

sql-create-index

Create Price Index

Problem Statement

Create an index named 'idx_price' on the Price column of the Products table to speed up price-based searches.

Dropping an Index

-- MySQL
DROP INDEX index_name ON table_name;

-- PostgreSQL / SQL Server
DROP INDEX index_name;

When to Use Indexes

Good for: Columns frequently used in WHERE, JOIN, ORDER BY.
Bad for: Columns with low cardinality (e.g., Gender: 'M'/'F'), small tables, or tables with frequent writes (INSERT/UPDATE/DELETE).

[!WARNING] Too many indexes can slow down INSERT, UPDATE, and DELETE operations because the indexes must also be updated.