Indexes, Selectivity and WHERE Conditions

Explanation

Indexes can improve read performance when queries filter on selective fields, but they are not a magic solution. A poor WHERE condition can still be slow even with indexes. Adding too many indexes can slow down inserts and updates and increase maintenance cost. Before requesting a custom index, analyze SQL trace, data volume, selectivity, existing indexes and business frequency. On HANA, indexing strategy differs from classical databases, but selective filtering still matters. The best performance improvement is often reducing data early through meaningful WHERE conditions.

Code example

* Weak query:* Date-only filter may still return huge data volume. SELECT * FROM bkpf INTO TABLE @DATA(lt_bkpf) WHERE budat IN @s_budat. * Better query:* Add selective business filters and select only required fields. SELECT bukrs, belnr, gjahr, budat, blart FROM bkpf INTO TABLE @lt_bkpf WHERE bukrs = @p_bukrs AND blart IN @s_blart AND budat IN @s_budat. * Key point:* Good WHERE design can reduce DB work before index discussion.

Real project scenario

A custom report filtered a large table only by date range and fetched millions of records. Adding company code and document type filters reduced data volume enough that no custom index was needed.

Common mistakes

- Requesting index before trace analysis. - Using SELECT *. - Filtering on non-selective fields only. - Adding too many custom indexes.

Best practices

- Use selective WHERE conditions. - Select only required fields. - Check existing indexes. - Validate need with ST05/DB analysis.

Interview angle

A strong answer should explain selectivity, existing indexes and ST05 evidence before creating indexes.