SQL - Indexes

Overview

Indexes speed lookups by organizing data for fast retrieval. They trade write performance and storage for read speed.

-- create index
CREATE INDEX idx_emp_dept ON employees(department_id);
-- drop index (MySQL)
DROP INDEX idx_emp_dept ON employees;

-- create index
CREATE INDEX idx_emp_dept ON dbo.employees(department_id);
-- drop index (SQL Server)
DROP INDEX idx_emp_dept ON dbo.employees;

-- create index
CREATE INDEX idx_emp_dept ON employees(department_id);
-- drop index (SQLite)
DROP INDEX idx_emp_dept;

Use EXPLAIN to check if queries use indexes.