SQL Indexes

An Index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns.

Think of an index like the index at the back of a book. If you want to find every reference to "Database" in a 1000-page book:

  • Without an Index: You have to flip through every single page (a "Full Table Scan").
  • With an Index: You look up "Database" in the index, get the specific page numbers, and go directly there.

Creating an Index

The CREATE INDEX statement is used to create an index on a table. Indexes are usually created on columns that are frequently used in the WHERE clause or JOIN conditions.

Syntax

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

Database Preparation

Run this block to set up the tables used in the examples below.

DROP TABLE IF EXISTS Employees;
CREATE TABLE Employees (
    EmployeeID INTEGER PRIMARY KEY,
    FirstName VARCHAR(100),
    LastName VARCHAR(100)
);

DROP TABLE IF EXISTS Users;
CREATE TABLE Users (
    ID INTEGER PRIMARY KEY,
    Email VARCHAR(255)
);

SELECT 'Database Ready' AS Status;

Creating an Index

Example

Operation: Create an index on the LastName column of the Employees table to speed up searches by name.

CREATE INDEX idx_lastname
ON Employees (LastName);

SELECT 'Index Created' AS Status;

Unique Indexes

You can create a Unique Index to not only speed up queries but also enforce uniqueness on a column (similar to a UNIQUE constraint).

CREATE UNIQUE INDEX idx_user_email
ON Users (Email);

SELECT 'Unique Index Created' AS Status;

When to Use Indexes (and When Not To)

Indexes are a trade-off. They speed up Read operations (SELECT) but slow down Write operations (INSERT, UPDATE, DELETE).

  • Pros: Significantly faster data retrieval.
  • Cons:
    • Takes up extra storage space on the disk.
    • The database must update the index every time you add or change data, adding overhead.

Best Practices:

  1. Index columns frequently used for searching (e.g., WHERE UserID = ...).
  2. Index columns used for joining tables (Foreign Keys).
  3. Do NOT index small tables. It's often faster for the database to just read the whole table.
  4. Do NOT index columns with frequent updates. The performance cost of maintaining the index might outweigh the reading benefits.

Removing an Index

If an index is no longer needed, you can delete it using the DROP INDEX command.

DROP INDEX idx_lastname;

SELECT 'Index Dropped' AS Status;

Hands-on Exercise

Task:

  1. Create a table named Products with ProductID (INTEGER PRIMARY KEY) and ProductName (VARCHAR(100)).
  2. Create an index named idx_product_name on the ProductName column of the Products table.

Your Query:

-- Write your query here

Expected Results:

Status
Table and Index Created