SQL Index | Academic Block

SQL INDEX

Learn how SQL indexes improve query performance by allowing the database to find rows more efficiently.

What is an INDEX?

An INDEX is a database object that helps the database find rows faster without scanning the entire table. Indexes are especially useful when columns are frequently used in WHERE, ORDER BY, or JOIN operations.

SQLite supports indexes and automatically creates indexes for certain constraints, such as PRIMARY KEY and UNIQUE.

Basic INDEX Syntax

Use CREATE INDEX to create an index on one or more columns.

DROP TABLE IF EXISTS books;

CREATE TABLE books (
  book_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author TEXT NOT NULL,
  price REAL
);

INSERT INTO books VALUES
  (1, 'SQL Basics', 'Riya Sharma', 450),
  (2, 'Database Design', 'Arjun Mehta', 620),
  (3, 'Web Development', 'Neha Kapoor', 550),
  (4, 'Python Guide', 'Vikram Rao', 700);

CREATE INDEX idx_books_author
ON books(author);

SELECT *
FROM books
WHERE author = 'Riya Sharma';

INDEX for a WHERE Condition

An index can be useful when a column is frequently searched using a WHERE condition.

DROP TABLE IF EXISTS products;

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  category TEXT NOT NULL,
  price REAL NOT NULL
);

INSERT INTO products VALUES
  (1, 'Wireless Mouse', 'Accessories', 850),
  (2, 'Mechanical Keyboard', 'Accessories', 2400),
  (3, 'Desk Lamp', 'Office', 1200),
  (4, 'USB Hub', 'Accessories', 650),
  (5, 'Office Chair', 'Furniture', 8500);

CREATE INDEX idx_products_category
ON products(category);

SELECT product_name, price
FROM products
WHERE category = 'Accessories'
ORDER BY price;

INDEX on Multiple Columns

A composite index contains more than one column. It can be useful when queries commonly filter by the same combination of columns.

DROP TABLE IF EXISTS sales;

CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL,
  region TEXT NOT NULL,
  amount REAL NOT NULL
);

INSERT INTO sales VALUES
  (1, 'Aarav', 'North', 4200),
  (2, 'Meera', 'South', 5800),
  (3, 'Kabir', 'North', 3100),
  (4, 'Isha', 'West', 7200),
  (5, 'Nisha', 'North', 4600);

CREATE INDEX idx_sales_region_customer
ON sales(region, customer_name);

SELECT *
FROM sales
WHERE region = 'North'
  AND customer_name = 'Nisha';

UNIQUE INDEX

A UNIQUE index prevents duplicate values from being stored in the indexed column or combination of columns.

DROP TABLE IF EXISTS users;

CREATE TABLE users (
  user_id INTEGER PRIMARY KEY,
  username TEXT NOT NULL,
  email TEXT NOT NULL
);

INSERT INTO users VALUES
  (1, 'aman01', 'aman@example.com'),
  (2, 'riya02', 'riya@example.com'),
  (3, 'kabir03', 'kabir@example.com');

CREATE UNIQUE INDEX idx_users_email
ON users(email);

SELECT *
FROM users
ORDER BY user_id;

INDEX for ORDER BY

An index can also be useful for queries that frequently sort or search using the same column.

DROP TABLE IF EXISTS courses;

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  course_name TEXT NOT NULL,
  duration_hours INTEGER NOT NULL
);

INSERT INTO courses VALUES
  (1, 'SQL Fundamentals', 20),
  (2, 'Web Design', 35),
  (3, 'Data Analytics', 45),
  (4, 'Computer Networks', 30),
  (5, 'Database Design', 40);

CREATE INDEX idx_courses_duration
ON courses(duration_hours);

SELECT course_name, duration_hours
FROM courses
ORDER BY duration_hours DESC;

Viewing Indexes with PRAGMA

SQLite provides the PRAGMA index_list statement to inspect the indexes associated with a table.

DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL,
  city TEXT NOT NULL
);

INSERT INTO customers VALUES
  (1, 'Aditi', 'Delhi'),
  (2, 'Rohan', 'Mumbai'),
  (3, 'Sana', 'Delhi'),
  (4, 'Karan', 'Pune');

CREATE INDEX idx_customers_city
ON customers(city);

PRAGMA index_list('customers');

Viewing INDEX Columns

The PRAGMA index_info statement can show which columns belong to a particular index.

DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL,
  city TEXT NOT NULL,
  order_total REAL NOT NULL
);

INSERT INTO orders VALUES
  (1, 'Aarav', 'Delhi', 2500),
  (2, 'Meera', 'Pune', 4200),
  (3, 'Kabir', 'Delhi', 1800),
  (4, 'Nisha', 'Mumbai', 3600);

CREATE INDEX idx_orders_city_name
ON orders(city, customer_name);

PRAGMA index_info('idx_orders_city_name');

Dropping an INDEX

Use DROP INDEX to remove an index that is no longer required. The table and its data remain unchanged.

DROP TABLE IF EXISTS inventory;

CREATE TABLE inventory (
  item_id INTEGER PRIMARY KEY,
  item_name TEXT NOT NULL,
  location TEXT NOT NULL
);

INSERT INTO inventory VALUES
  (1, 'Camera', 'Shelf A'),
  (2, 'Tripod', 'Shelf B'),
  (3, 'Microphone', 'Shelf A');

CREATE INDEX idx_inventory_location
ON inventory(location);

SELECT *
FROM inventory
WHERE location = 'Shelf A';

DROP INDEX idx_inventory_location;

SELECT *
FROM inventory
ORDER BY item_id;

Checking Query Performance with EXPLAIN QUERY PLAN

SQLite provides EXPLAIN QUERY PLAN to show how a query is expected to access the table. This can help you see whether an index can be used.

DROP TABLE IF EXISTS articles;

CREATE TABLE articles (
  article_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL
);

INSERT INTO articles VALUES
  (1, 'SQL Introduction', 'Database'),
  (2, 'HTML Basics', 'Web'),
  (3, 'SQL Indexes', 'Database'),
  (4, 'CSS Layout', 'Web'),
  (5, 'SQL Joins', 'Database');

CREATE INDEX idx_articles_category
ON articles(category);

EXPLAIN QUERY PLAN
SELECT *
FROM articles
WHERE category = 'Database';

INDEX with a JOIN

Indexes can be useful on columns that are frequently used to connect rows between tables.

DROP TABLE IF EXISTS departments;
DROP TABLE IF EXISTS staff;

CREATE TABLE departments (
  department_id INTEGER PRIMARY KEY,
  department_name TEXT NOT NULL
);

CREATE TABLE staff (
  staff_id INTEGER PRIMARY KEY,
  staff_name TEXT NOT NULL,
  department_id INTEGER
);

INSERT INTO departments VALUES
  (1, 'Engineering'),
  (2, 'Design'),
  (3, 'Support');

INSERT INTO staff VALUES
  (101, 'Arjun', 1),
  (102, 'Meera', 2),
  (103, 'Kabir', 1),
  (104, 'Sana', 3);

CREATE INDEX idx_staff_department
ON staff(department_id);

SELECT
  staff.staff_name,
  departments.department_name
FROM staff
INNER JOIN departments
  ON staff.department_id = departments.department_id
ORDER BY staff.staff_id;

Common INDEX Commands

Command	Purpose	Example
CREATE INDEX	Create an index	CREATE INDEX idx_name ON table_name(column_name)
CREATE UNIQUE INDEX	Create an index that prevents duplicate combinations	CREATE UNIQUE INDEX idx_email ON users(email)
DROP INDEX	Remove an index	DROP INDEX idx_name
PRAGMA index_list	List indexes for a table	PRAGMA index_list(‘users’)
PRAGMA index_info	Show columns in an index	PRAGMA index_info(‘idx_name’)

Advantages of INDEX

Can make searches on large tables faster.
Can improve queries using WHERE conditions.
Can help queries that frequently use JOIN conditions.
Can help with certain ORDER BY operations.
Can enforce uniqueness when a UNIQUE index is used.

Best Practices

Create indexes on columns frequently used for searching or joining.
Avoid creating unnecessary indexes because they require additional storage.
Remember that indexes can increase the cost of INSERT, UPDATE, and DELETE operations.
Consider composite indexes when queries commonly filter by multiple columns.
Use EXPLAIN QUERY PLAN to understand how SQLite executes important queries.
Do not assume every column needs an index.

The INDEX object can significantly improve database query performance by helping SQLite locate matching rows more efficiently.

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click “Run Code” to see the result on the right.

📝 SQL Code

👁️ Preview (query result)

Click “Run Code” to see the result here.