Indexes & Query Performance

1. How do you optimize a slow-running SQL query?

Start by checking the query execution plan, then fix the bottleneck. The most common fix is adding an index on the columns used in WHERE and JOIN.

Step 1 — look at the execution plan: The database can show you how it plans to run the query. Use EXPLAIN in PostgreSQL or MySQL.

EXPLAIN ANALYZE
SELECT id, name FROM employees WHERE department_id = 5;

The plan shows whether the database is scanning the whole table or using an index. A full table scan over millions of rows is usually the main problem.

Step 2 — apply the common fixes:

Add indexes: An index on department_id lets the database jump straight to matching rows instead of reading everything.

CREATE INDEX idx_emp_dept ON employees(department_id);

Avoid SELECT *: Only select the columns you need. This reduces the data transferred.

Make conditions index-friendly: Don’t wrap indexed columns in functions. WHERE LOWER(name) = 'ali' blocks the index. Use WHERE name = 'Ali' instead.

Example — before and after:

Without an index, the query reads every row:

SELECT id, name FROM employees WHERE department_id = 5;

With an index on department_id, the same query finds the rows instantly.

Key takeaway: Optimization is iterative. Change one thing, re-run the plan, and measure. Also remember that too many indexes slow down INSERT and UPDATE, so index only what slow queries actually need.

2. What is the difference between a Clustered and a Non-Clustered index?

A clustered index decides the physical order of the data rows in a table. A non-clustered index is a separate structure that points to the data rows.

Clustered index: Think of a dictionary. The words are physically sorted alphabetically. The book is stored in that order.

A clustered index works the same way. The actual data rows are stored sorted by the indexed column.

Because the data itself is rearranged, a table can have only one clustered index.

A primary key is usually the clustered index.

Non-clustered index: Think of the index at the back of a textbook. It’s a separate list that says “topic X is on page 42”. The book’s pages aren’t rearranged.

A non-clustered index is a separate structure. It holds the indexed column values and pointers to the actual data rows.

A table can have many non-clustered indexes.

Key differences table:

	Clustered	Non-clustered
Sorts the actual data	Yes	No
Separate structure	No	Yes
How many per table	One	Many
What the primary key usually is	Yes	No
Speed of lookup	Faster	Slower (extra pointer step)

Key takeaway: Clustered indexes rearrange the table itself, so there can be only one. Non-clustered indexes are extra lookup tables, so you can have many.

3. How does an index improve performance, and what are its drawbacks?

An index speeds up read operations by letting the database find rows quickly. But it slows down write operations and takes extra storage.

How it helps reads: Without an index, the database must read every row to find what you need. That’s called a full table scan.

With an index, the database jumps straight to the matching rows, like using a book’s index instead of reading every page.

Example — without and with an index:

SELECT * FROM employees WHERE department_id = 5;

Without an index, the database scans all one million rows.

With an index on department_id, it finds the matching rows almost instantly.

The drawbacks: Every INSERT, UPDATE, and DELETE must also update the index.

More indexes mean more work on every write.

Each index also uses disk space.

Key differences table:

	With index	Without index
SELECT speed	Fast	Slow on big tables
INSERT/UPDATE/DELETE speed	Slower	Faster
Storage used	Extra	None

Key takeaway: Indexes trade write speed and storage for read speed. Add them on columns you actually query often, not on every column.

4. When should you avoid creating an index?

Avoid indexes on small tables, on frequently updated tables, and on columns with very few unique values.

1. Small tables: If a table has only 50 rows, the database can read all of them instantly.

An index adds overhead without helping. Scanning the whole table is already fast.

2. Frequently updated tables: Every insert, update, and delete must maintain the index.

If writes happen constantly, the index maintenance cost can be higher than the speed gain.

3. Low-cardinality columns: Cardinality means how many unique values a column has.

A Gender column has only two unique values: male and female.

An index on it doesn’t help much, because almost every row matches. The database might still scan the whole table.

Key takeaway: Only index columns that are highly selective (many unique values) and queried often. Indexing everything slows the database down.

5. How do you use the EXPLAIN command?

EXPLAIN shows the query execution plan — how the database intends to run your query.

What it tells you:

• Whether the database scans the whole table.
• Which indexes it uses.
• Where the expensive operations are.

Example:

EXPLAIN SELECT * FROM employees WHERE department_id = 5;

The output might say “Seq Scan on employees” (a full table scan) or “Index Scan using idx_emp_dept” (an index is used).

Why it matters: If you see a full table scan on a large table, you know you need an index.

After adding the index, run EXPLAIN again to confirm the plan changed.

EXPLAIN ANALYZE actually runs the query and shows real timings.

Key takeaway: EXPLAIN is the first tool for optimizing slow queries. It shows the plan; you fix the bottleneck; re-check and repeat.

6. Why is SELECT * generally discouraged?

SELECT * fetches every column from the table. That causes unnecessary work and can break your app.

The problems:

1. Unnecessary data: If a table has 30 columns but you need 2, SELECT * pulls all 30.

This increases network traffic and memory use.

2. Breaks on schema change: If someone adds or renames a column, the result set changes.

Code that assumed a fixed column order can break.

3. Can’t use covering indexes efficiently: An index that contains exactly the columns you need can answer a query without touching the table. SELECT * forces a trip to the full table.

Good vs bad:

-- Bad: pulls everything
SELECT * FROM employees;

-- Good: only what's needed
SELECT name, salary FROM employees;

Key takeaway: Always list the columns you actually need. It’s faster, uses less memory, and protects against schema changes.

7. What is an Index and why is it used?

An index is a data structure that speeds up data retrieval. It lets the database find rows without scanning the whole table.

The analogy: A book’s index at the back tells you “topic X is on page 42”.

Without it, you read every page.

An SQL index works the same way.

Example:

CREATE INDEX idx_emp_dept ON employees(department_id);

Now this query is fast:

SELECT * FROM employees WHERE department_id = 5;

The database jumps straight to department 5’s rows instead of scanning everything.

The trade-offs:

• Extra storage.
• Slower writes (index must be updated).

Key takeaway: Indexes make reads fast but cost storage and slow writes. Index columns you query often — not every column.

8. Why is ‘SELECT *’ discouraged in production?

SELECT * fetches every column, which wastes resources and can break when the schema changes.

The problems:

1. Unneeded data: If a table has 30 columns and you need 2, SELECT * pulls all 30.

More network traffic, more memory.

2. Breaks on schema change: Adding or renaming a column changes the result.

Code that assumed the old shape breaks.

3. Blocks covering indexes: An index with exactly your needed columns can answer a query alone. SELECT * forces a trip to the full table.

Good vs bad:

-- Bad
SELECT * FROM employees;

-- Good
SELECT name, salary FROM employees;

Key takeaway: List the columns you need. Faster, lighter, and safer against schema changes.

9. What is a ‘Full Table Scan’?

A full table scan is when the database reads every row in a table to find the requested data.

When it happens:

• No useful index exists on the column.
• The query condition doesn’t match any index.
• The database decides scanning everything is faster than using an index.

Why it’s slow: On a table with millions of rows, reading every row takes time.

How to fix it: Add an index on the filtered column.

CREATE INDEX idx_emp_dept ON employees(department_id);

Now a lookup on department_id uses the index instead of scanning.

Key takeaway: A full table scan reads everything. Indexes let the database skip it — that’s their whole purpose.