Worth being precise about one thing before starting: an index isn't technically one of the six constraint types from this module's first lesson, it doesn't reject or validate any data at all. It's grouped here because UNIQUE and PRIMARY KEY, two real constraints, both create one automatically behind the scenes to do their job fast, so understanding indexes properly closes the loop on what those constraints were actually relying on.
Learning Objectives
- Explain what an index actually speeds up, and what it costs in return.
- Create a regular index and a unique index, and state the real difference.
- Drop an index correctly on every database in this course.
WHERE / JOIN / ORDER BY
On an indexed column
Index
A fast lookup structure, not the table itself
Matching Rows
Found directly, no full table scan
What an Index Actually Does
Without an index, finding every employees row with department_id = 2 means the database checks every single row in the table, a full table scan, fine for a handful of rows, genuinely slow once employees holds tens of thousands. An index on department_id is a separate, sorted lookup structure the database maintains alongside the table, letting it jump directly to the matching rows instead of scanning all of them.
This is precisely why UNIQUE and PRIMARY KEY are fast to filter on, from the Module 8 lessons earlier: both create an index automatically the moment they're declared, to make their own duplicate-checking fast in the first place.
Creating a Regular Index
department_id is queried constantly, every JOIN against departments and every WHERE department_id = ... in this course's earlier modules benefits from this index existing. A regular index speeds up reads, it doesn't restrict what values can be stored, unlike UNIQUE.
CREATE INDEX idx_employees_department_id ON employees(department_id);
Creating a Unique Index
This looks almost identical to a regular index, but UNIQUE here means the database also rejects a duplicate email value, the exact mechanism SQLite's ADD CONSTRAINT-less UNIQUE workaround from the unique-constraint lesson relies on. A UNIQUE constraint and a UNIQUE index end up doing the identical thing in practice, a constraint is really just a UNIQUE index plus a label describing its intent.
CREATE UNIQUE INDEX idx_employees_email ON employees(email);
A Composite Index
An index can span multiple columns too, speeding up queries that filter or join on that combination together, like finding a specific employee's specific project assignment. Column order matters here, this index speeds up lookups by employee_id alone or by employee_id and project_id together, but not by project_id alone.
CREATE INDEX idx_assignments_lookup ON project_assignments(employee_id, project_id);
The Real Cost: Indexes Aren't Free
- + Dramatically faster SELECT queries that filter, join, or sort on the indexed column.
- + Enforces uniqueness for free when created as a UNIQUE index.
- + The database's query planner picks it automatically, no change needed to the SELECT statements themselves.
- − Every INSERT, UPDATE, or DELETE on an indexed column has to update the index too, not just the table.
- − Each index consumes real additional storage, on top of the table's own data.
- − An index on a rarely-queried column, or one with very few distinct values, often isn't worth its write-performance cost.
Dropping an Index: A Real Syntax Split
| Database | Drop an Index |
|---|---|
| MySQL / MariaDB | DROP INDEX idx_name ON employees; |
| PostgreSQL / SQLite | DROP INDEX idx_name; |
| SQL Server | DROP INDEX idx_name ON employees; |
| Oracle | DROP INDEX idx_name; |
MySQL, MariaDB, and SQL Server all require naming the table the index belongs to. PostgreSQL, SQLite, and Oracle don't, their index names are unique across the whole schema/database already, so the table is implied. Mixing these up (DROP INDEX idx_name; on MySQL, for example) fails with a syntax error.
Try It Yourself
Write a regular (non-unique) index on projects.name, named idx_projects_name, on PostgreSQL.
CREATE INDEX idx_projects_name ON projects(name);
Frequently Asked Questions
Does every column need its own index?
No, indexing every column is actually counterproductive, each one adds write overhead and storage with no benefit unless that specific column is actually used in WHERE, JOIN, or ORDER BY. Index the columns real queries actually filter or join on, not everything by default.
Is a UNIQUE constraint the same thing as a UNIQUE index?
In practice, yes, declaring UNIQUE as a constraint creates a UNIQUE index automatically behind the scenes to enforce it. The difference is really just naming and intent, a constraint describes a business rule, while CREATE UNIQUE INDEX describes it as a performance structure, but the enforcement is identical either way.
Common Beginner Mistakes
Adding an index to every column "just in case"
Each index slows down every INSERT, UPDATE, and DELETE on that table, since the index itself has to be updated too, and consumes real storage on top of the table's own data. Index deliberately, based on actual query patterns, not reflexively on every column.
Using MySQL's DROP INDEX syntax on PostgreSQL, or vice versa
MySQL and SQL Server require DROP INDEX idx_name ON table;, PostgreSQL, SQLite, and Oracle use just DROP INDEX idx_name; with no table named. Running the wrong form on the wrong database is a syntax error, not a silent no-op.
Best Practices
- Index columns that real WHERE, JOIN, or ORDER BY clauses actually use, not every column by default.
- Prefer letting UNIQUE and PRIMARY KEY create their own indexes automatically, rather than also manually creating a duplicate one.
- For a composite index, put the column most often filtered on alone first, column order affects which queries actually benefit.
Interview Questions
What's the actual tradeoff an index introduces, and why isn't every column just indexed by default?
An index speeds up SELECT queries that filter, join, or sort on that column, by letting the database jump directly to matching rows instead of scanning the whole table. In exchange, every INSERT, UPDATE, and DELETE on that column now has to update the index too, and the index itself consumes additional storage. Indexing every column by default slows down writes across the whole table for columns that may never actually benefit a real query.
Write a composite index on project_assignments(employee_id, project_id), and explain what query pattern it speeds up.
CREATE INDEX idx_assignments_lookup ON project_assignments(employee_id, project_id); — this speeds up queries filtering by employee_id alone, or by employee_id and project_id together (like finding one employee's one specific assignment), but not a query filtering by project_id alone, since that column isn't first in the index's column order.
Summary
An index is a separate lookup structure that speeds up SELECT queries filtering, joining, or sorting on a column, at the cost of extra storage and slower writes. UNIQUE and PRIMARY KEY both create one automatically, a manual CREATE INDEX (or CREATE UNIQUE INDEX) targets any other column worth speeding up, chosen based on real query patterns, not reflexively applied everywhere. DROP INDEX's exact syntax genuinely splits by database, whether the table name is required or implied.
What's Next?
AUTO INCREMENT closes out this module, the full dedicated version of the id-generating pattern every CREATE TABLE statement since Module 7 has already been using.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.