What are indexes in SQL? | SQL Interview Questions

What You'll Learn

What indexes are
Types of indexes
When to use them

What are Indexes?

Indexes are data structures that speed up data retrieval, like a book's index.

Creating Indexes

query.sqlSQL

-- Single column index
CREATE INDEX idx_users_email ON users(email);

-- Composite index (multiple columns)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

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

-- Drop index
DROP INDEX idx_users_email ON users;

Types of Indexes

B-Tree (Default)

query.sqlSQL

-- Good for: =, <, >, <=, >=, BETWEEN, LIKE 'prefix%'
CREATE INDEX idx_price ON products(price);

Hash Index

query.sqlSQL

-- Good for: = (equality only)
CREATE INDEX idx_status ON orders USING HASH(status);

Full-Text Index

query.sqlSQL

-- Good for: text search
CREATE FULLTEXT INDEX idx_content ON articles(title, content);
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('sql tutorial');

When to Index

Good candidates:

Columns in WHERE clauses
Columns in JOIN conditions
Columns in ORDER BY
Foreign keys

Avoid indexing:

Small tables
Frequently updated columns
Low cardinality columns (few unique values)

Checking Index Usage

query.sqlSQL

-- MySQL
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com';