A query on a table with a million rows can take seconds or milliseconds depending on one thing: whether the database can find rows through an index. Indexes are the most important performance tool most analysts and developers use.
The book analogy
Without an index, finding a topic in a book means reading every page: a full table scan. An index is like the book’s index at the back, which points straight to the right page. It is extra work to maintain, but lookups become much faster.
Creating an index
CREATE INDEX idx_orders_customer ON orders(customer_id);
Now a query such as SELECT * FROM orders WHERE customer_id = 42 can jump straight to matching rows.
When an index helps
- Columns used often in WHERE conditions, such as customer_id or order_date.
- Columns used in JOIN conditions, such as foreign keys.
- Columns used for ORDER BY on large results.
When it does not
- Small tables, where scanning is already fast.
- Columns with very few distinct values, such as a yes/no flag, unless combined with others.
- Tables with heavy writes, because every insert and update must also update the index.
Patterns that stop an index being used
- Wrapping the column in a function:
WHERE YEAR(order_date) = 2026. Write a range instead. - A leading wildcard:
LIKE '%sharma'cannot use a normal index. - Implicit type conversion, such as comparing a text column with a number.
Check the plan
Put EXPLAIN in front of a query (EXPLAIN SELECT ...) to see whether the database plans to use an index or scan the whole table. In PostgreSQL, use EXPLAIN ANALYZE to see real timings.
Composite indexes
For queries filtering on two columns, such as customer_id and order_date, one index on both (in that order) often works better than two separate ones. Put the column you filter with equality first.
Related: SQL date functions for data analysts, SQL subqueries vs CTEs and SQL JOINs explained.
SQL performance is practised with real datasets in our 5-month Data Analytics course in Bangalore.
Book a free demo class at our BTM Layout centre, and ask a counsellor for batch details.
