SELECT *
FROM customers
WHERE UPPER(customer_name) = 'RAHUL';

If you have a normal index on:

customer_name

the database may not be able to use that index efficiently because the query applies a function to the column.

Depending on the database, an expression/function-based index may help:

CREATE INDEX idx_customer_upper_name
ON customers(UPPER(customer_name));

The exact syntax and availability depend on the database.

1️⃣7️⃣ Indexes and NULL

Indexes can have database-specific behavior regarding NULL values.

For example:

SELECT *
FROM customers
WHERE email IS NULL;

Whether and how an index can help depends on the database's index implementation.

Don't assume every database handles NULL indexing identically.

1️⃣8️⃣ Why Not Create an Index on Every Column?

This is a common beginner mistake.

You might think:

More indexes

Faster database

But that's not true.

Indexes have costs.

When data changes:

• INSERT

• UPDATE

• DELETE

the database may also need to maintain the relevant indexes.

Therefore:

More indexes

↓

More storage

↓

More maintenance

↓

Potentially slower writes

Indexes should be created based on actual query patterns and workload requirements.

1️⃣9️⃣ Indexes Have a Storage Cost

Suppose your table contains:

100 million rows

and you create several large indexes.

The indexes themselves can consume significant storage.

So database design involves a trade-off:

Read performance

↕

Write performance

↕

Storage

A good indexing strategy balances all three.

20️⃣ Query Performance

Indexes are only one part of SQL performance.

Other factors include:

• Query structure

• JOIN strategy

• Filtering

• Data volume

• Table design

• Statistics

• Partitioning

• Database engine

• Execution plan

• Network transfer

• Aggregations

• Sorting

• Data types

A slow query isn't automatically an "index problem."

21️⃣ SELECT * and Performance

Consider:

SELECT *
FROM orders
WHERE customer_id = 101;

If you only need:

• order_id

• order_date

• order_amount

prefer:

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

Why?

Because retrieving unnecessary columns can:

• Increase data transfer

• Increase memory usage

• Increase I/O

• Make downstream processing heavier

It also makes your SQL less explicit.

22️⃣ Filter Early

Suppose you need sales for 2026:

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

Filtering before aggregation can significantly reduce the amount of data that needs to be processed.

Conceptually:

10 million rows

↓

Filter

↓

2 million rows

↓

Aggregate

instead of:

10 million rows

↓

Aggregate everything

↓

Filter later

The optimizer may transform queries internally, but writing clear predicates is still important.

23️⃣ Avoid Unnecessary Data Processing

Suppose you need only completed transactions:

SELECT
    transaction_id,
    amount
FROM transactions
WHERE status = 'Completed';