Sql Indexes — Free Sql Tutorial

TL;DR: Learn Sql Indexes in Sql with a free, beginner-friendly tutorial, examples and practice for Indian students on Syllab.in.

Sql Indexes in Sql

An index is a data structure that speeds up SELECT queries at the cost of slightly slower INSERT/UPDATE/DELETE and extra storage. Think of it like the index at the back of a textbook — you find the page without reading every page.

CREATE INDEX creates a non-unique index. CREATE UNIQUE INDEX ensures no duplicate values. The PRIMARY KEY automatically creates a unique index.

B-tree indexes (default) work for =, <, >, BETWEEN, LIKE "abc%" queries. They don't help for LIKE "%abc" (leading wildcard).

Use EXPLAIN (MySQL) or EXPLAIN ANALYZE (PostgreSQL) to see if your query is using an index or doing a full table scan.

-- Create index on one column
CREATE INDEX idx_name ON table(column);

-- Composite index (multiple columns)
CREATE INDEX idx_name ON table(col1, col2);

-- Unique index
CREATE UNIQUE INDEX idx_email ON users(email);

-- Drop index
DROP INDEX idx_name ON table;  -- MySQL
DROP INDEX idx_name;           -- PostgreSQL

-- Check query plan
EXPLAIN SELECT * FROM table WHERE column = value;

Learn Sql Indexes step by step with Syllab's free interactive Sql tutorial — runnable code examples, practice exercises and instant AI feedback, all free with no signup. Explore the full Sql course →