Primary vs secondary indexes in DBMS Theory - Visual Side-by-Side Comparison

Concept Flow - Primary vs secondary indexes

Start Query

↓

Check Primary Index

Yes / No↓

Use Primary

↓

Fast Access

↓

Use Secondary

↓

Slower Access

↓

Retrieve Data

↓

Return Result

When a query runs, the system first looks for a primary index to find data quickly. If none matches, it checks secondary indexes. If no index helps, it scans the whole table.

Execution Sample

DBMS Theory

Query: Find record with ID=5
Check primary index on ID
If found, get record fast
Else check secondary index
If found, get record
Else scan whole table

This example shows how a database uses primary and secondary indexes to find a record efficiently.

Analysis Table

Step	Action	Index Checked	Result	Next Step
1	Start query to find ID=5	None	N/A	Check primary index
2	Check primary index on ID	Primary index on ID	Found entry for ID=5	Retrieve record directly
3	Retrieve record using primary index	Primary index	Record found quickly	Return result
4	Return result to user	N/A	Query complete	End

💡 Primary index found the record, so no need to check secondary indexes or scan table.

State Tracker

Variable	Start	After Step 2	After Step 3	Final
Index Checked	None	Primary index	Primary index	Primary index
Record Found	No	Yes	Yes	Yes
Access Method	None	Index lookup	Direct retrieval	Complete

Key Insights - 3 Insights

Why does the query check the primary index before the secondary index?

Because the primary index is usually faster and unique, so checking it first can quickly find the record without extra work, as shown in execution_table step 2.

What happens if the primary index does not have the record?

The system then checks the secondary index to try to find the record, which may be slower, as implied by the concept_flow after 'No' from primary index.

Why might a full table scan be needed?

If neither primary nor secondary indexes help, the database must scan all records, which is slower, shown in concept_flow as the last step.

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the primary index checked?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Refer to execution_table row where 'Check primary index on ID' is the action.

According to variable_tracker, what is the value of 'Record Found' after step 2?

A Yes

B No

C Unknown

D N/A

💡 Hint

Check the 'Record Found' row under 'After Step 2' column in variable_tracker.

If the primary index did not find the record, what would be the next action according to concept_flow?

A Return result immediately

B Scan whole table

C Check secondary index

D End query

💡 Hint

Look at concept_flow after 'No' from 'Check Primary Index' decision.

Concept Snapshot

Primary index: Unique, fast lookup on main key.
Secondary index: Non-unique, slower lookup on other columns.
Query tries primary index first, then secondary.
If no index helps, full table scan occurs.
Primary index access is fastest and preferred.

Full Transcript

When a database query runs, it first checks the primary index because it is unique and fast. If the record is found there, it retrieves it directly, making the query efficient. If not found, it looks at secondary indexes, which may be slower and non-unique. If no index helps, the database scans the entire table, which is slow. This flow ensures queries use the fastest method available to find data.