SQL INDEX
An index is a separate data structure that lets the database find rows by a column without scanning the whole table.
Create a basic index
SQL
CREATE INDEX idx_customers_email ON customers (email);
Unique index
SQL
CREATE UNIQUE INDEX uq_customers_email ON customers (email);
A unique index doubles as a UNIQUE constraint.
Composite index
SQL
CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at);
Composite indexes are usable for queries that filter on the leading column(s) — WHERE customer_id = … uses the index; WHERE created_at = … alone usually doesn't.
What to index
| Add an index when | Skip the index when |
|---|---|
| The column is filtered or joined often. | The table is small (a few thousand rows). |
| The column has high cardinality. | Almost every row has the same value. |
| Sorting / grouping by that column is common. | The table is write-heavy and read-rarely. |
The cost
Every index adds disk space and slows writes (insert/update/delete must touch every relevant index). Adding "just in case" indexes is a common performance regression.
Drop an index
SQL
DROP INDEX idx_customers_email; -- standard DROP INDEX idx_customers_email ON customers; -- MySQL syntax
Tip: Use
EXPLAIN (MySQL/PG/SQLite) or "Display Estimated Execution Plan" (SQL Server) to see whether your index is actually being used. If not, the index isn't pulling its weight.Example
Exercise
Make a unique index on the email column.
CREATE
INDEX idx_email ON customers (email);
Six letters; same word as the constraint.
Discussion
Loading…