Why?

Because the query filters on:

customer_id

• order_date

The database can then evaluate the index as part of its execution strategy.

But you should verify the impact using an execution plan and real workload data.

30️⃣ Query Optimization Checklist

When you have a slow SQL query, ask:

Step 1

Do I actually need all columns? SELECT * may be unnecessary.

Step 2

Can I filter earlier? WHERE can reduce the amount of data processed.

Step 3

Are JOIN conditions correct? Check: ON a.id = b.id

Step 4

Could a suitable index help? Look at frequently used WHERE, JOIN, ORDER BY columns.

Step 5

Is the index being used? Check the execution plan.

Step 6

Am I processing unnecessary rows? Look at the data volume.

Step 7

Are functions preventing efficient access? For example: WHERE UPPER(name) = ...

Step 8

Am I creating too many indexes? Indexes also have costs.

💼 Data Analyst Example

Imagine a dashboard queries:

SELECT
    customer_id,
    SUM(order_amount) AS total_sales
FROM orders
WHERE order_date >= DATE '2026-01-01'
GROUP BY customer_id;

The table contains:

100 million orders

Potential performance considerations include:

1. Is order_date indexed?

2. How selective is the date filter?

3. How many rows are processed?

4. Is partitioning available?

5. What does EXPLAIN show?

6. Is the aggregation expensive?

7. Is the dashboard requesting this data repeatedly?

A strong analyst doesn't immediately say:

«"Create an index."»

Instead, they investigate the execution plan and workload first.

🎯 Interview Questions

Q1. What is an index?

An index is a database structure that can help locate rows more efficiently.

Q2. Why are indexes useful?

They can improve read performance for suitable queries, especially when searching or joining on indexed columns.

Q3. Can indexes slow down INSERT operations?

Yes. The database may need to maintain indexes when rows are inserted.

Q4. Can indexes slow down UPDATE and DELETE?

Yes, depending on which indexed columns are affected and the database implementation.

Q5. What is a composite index?

An index containing multiple columns.

CREATE INDEX idx_customer_date
ON orders(customer_id, order_date);

Q6. Does the order of columns in a composite index matter?

Yes. The leading columns strongly influence which queries can efficiently use the index.

Q7. Does every query use an index if one exists?

No. The optimizer decides whether using an index is beneficial.

Q8. What is EXPLAIN?

It is a command or feature used to inspect a query's execution plan, with syntax varying by database.

Q9. What is selectivity?

It describes how effectively a condition narrows the number of matching rows.

Q10. Why shouldn't you index every column?

Indexes consume storage and require maintenance during data modifications, so excessive indexing can hurt write performance.

🧠 Practice Questions

Practice 1

Create an index on "customer_id":

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

Practice 2

Create a composite index using customer and date:

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

Practice 3

Create a unique index on email:

CREATE UNIQUE INDEX idx_customers_email
ON customers(email);

Practice 4

Write a query that could potentially benefit from an index on "customer_id":

SELECT
    order_id,
    order_date,
    order_amount
FROM orders
WHERE customer_id = 101;

Practice 5

Inspect the execution plan: