Don't retrieve every transaction and filter it later in Python or Excel if the database can efficiently perform the filtering.

A good principle is:

«Let the database do the data filtering and aggregation whenever practical.»

24️⃣ Execution Plans

One of the most important tools for understanding SQL performance is the execution plan.

An execution plan shows how the database intends to execute your query.

It can reveal things such as:

• Table Scan

• Index Scan

• Index Seek

• Join Strategy

• Sort

• Aggregation

• Estimated Rows

• Actual Rows

• Cost

Different databases use different terminology.

25️⃣ EXPLAIN

Many SQL databases support "EXPLAIN".

For example:

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 101;

This lets you inspect the planned execution strategy.

Some databases support:

EXPLAIN ANALYZE

which can provide information about actual execution as well.

Exact syntax and output vary by database.

26️⃣ Table Scan vs Index Access

Imagine a table with:

10,000,000 rows

A table scan may mean the database reads a large portion of the table to find matching records.

Conceptually:

10 million rows

↓

Check rows

↓

Find matching rows

An index-based access path may instead look more like:

Index

↓

Locate matching keys

↓

Fetch relevant rows

For highly selective queries, the second approach can be much more efficient.

But if a query needs a large percentage of the table, scanning the table may actually be more efficient.

This is why the optimizer chooses the execution strategy.

27️⃣ Selectivity

Selectivity describes how effectively a condition narrows down the data.

Consider:

WHERE customer_id = 100245

If customer IDs are unique, this may return one row.

Highly selective.

Now consider:

WHERE country = 'India'

If 60% of the table contains Indian customers, the condition is much less selective.

The database may decide that scanning the table is cheaper than using an index.

Therefore:

«An index isn't automatically useful simply because the column appears in WHERE.»

28️⃣ Indexes on Low-Cardinality Columns

Suppose:

status

contains only:

• Active

• Inactive

That's a low-cardinality column.

An index may not always provide a large benefit if most rows match the condition.

For example:

WHERE status = 'Active'

If 95% of rows are Active, reading the index and then retrieving almost the entire table may be less efficient than scanning the table.

Again, the optimizer makes the decision.

29️⃣ Real-World Example

Suppose an orders table contains:

50 million rows

Analysts frequently run:

SELECT
    order_id,
    order_date,
    order_amount
FROM orders
WHERE customer_id = 12345
  AND order_date >= DATE '2026-01-01';

A possible candidate is:

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);