Indexing and Query Optimization

This topic covers performance. You compare B+ tree and hash indexes, distinguish clustered from non-clustered and covering indexes, understand selectivity, and follow how a cost-based optimiser estimates and chooses among execution plans.

Relation 1 —

Write this relation with symbols exactly as in Database System Concepts — Korth, Silberschatz & Sudarshan before substituting numbers. Examiners award partial marks for a correct setup even when arithmetic slips.

Relation 2 —

Write this relation with symbols exactly as in Database System Concepts — Korth, Silberschatz & Sudarshan before substituting numbers. Examiners award partial marks for a correct setup even when arithmetic slips.

Relation 3 —

Write this relation with symbols exactly as in Database System Concepts — Korth, Silberschatz & Sudarshan before substituting numbers. Examiners award partial marks for a correct setup even when arithmetic slips.

Without an index, finding rows means scanning the whole table; an index provides a faster access path at the cost of extra storage and slower writes. A B+ tree keeps keys sorted so it supports both point lookups and range scans in logarithmic time, and its leaves are linked for efficient ranges — which is why it is the default. Hash indexes are faster for exact-match queries but useless for ranges. A clustered index dictates the physical row order (so there is only one per table), and a covering index answers a query entirely from the index without touching the table. The optimiser weighs these options using selectivity statistics, choosing an index only when it filters enough rows to beat a scan.

State assumptions explicitly before using any relation for indexing and query optimization — steady state, uniform properties, linear elastic material, ideal gas, incompressible flow, etc., as applicable.

Wrong assumptions invalidate the entire solution even when the formula is correct. In Database Systems viva and GATE descriptive questions, listing valid assumptions often earns separate marks.

1. Read the question and list given data with SI units (common in Database Systems papers).
2. Draw a neat labelled diagram where applicable — examiners in Indian universities award diagram marks even when arithmetic slips.
3. Identify which relation from this topic applies to indexing and query optimization.
4. Use equation 1: .
5. Use equation 2: .
6. Substitute values, compute, and verify units and sign (direction).
7. State conclusion in one line — e.g. safe/unsafe, stable/unstable, feasible/infeasible.

Indexing and Query Optimization appears in enterprise applications. In Indian it software curricula this topic is tested because it connects theory to relational model and SQL.

GATE and semester exams often combine indexing and query optimization with earlier units — revise prerequisites before attempting mixed problems.

Industry interview panels sometimes ask: "Where did you use indexing and query optimization?" — answer with a lab, mini-project, or plant visit example if possible.

Students use a hash index expecting range-query support, assume more indexes are always better (they slow writes), and forget that only one clustered index can exist per table. Believing an index always beats a full scan even for low-selectivity predicates is a common misconception.

Before attempting indexing and query optimization problems, confirm you can:

1. Hash indexes suit equality, not range, queries
2. A covering index contains every column a query needs
3. A cost-based optimiser uses statistics to pick a plan

Revise the solved examples in Database System Concepts — Korth, Silberschatz & Sudarshan and one previous-year GATE or university paper for this unit.