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.

Practice

1. What is the main purpose of a primary index in a database?

easy

A. To provide unique and fast access to records using the primary key

B. To speed up searches on non-key columns

C. To store duplicate values for faster retrieval

D. To backup the database automatically

Solution

1. Step 1: Understand the role of primary index

   A primary index is created on the primary key of a table, which uniquely identifies each record.

2. Step 2: Identify its main function

   It ensures fast and unique access to records based on the primary key values.

3. Final Answer:

   To provide unique and fast access to records using the primary key -> Option A

4. Quick Check:

   Primary index = unique fast access [OK]

Hint: Primary index = unique key fast access [OK]

Common Mistakes:

• Confusing primary index with secondary index
• Thinking primary index allows duplicates
• Assuming primary index is for backup

2. Which of the following is the correct statement about creating a secondary index in SQL?

easy

A. CREATE INDEX idx_name ON table(column);

B. CREATE UNIQUE INDEX idx_name ON table(column);

C. CREATE PRIMARY INDEX idx_name ON table(column);

D. CREATE SECONDARY INDEX idx_name ON table(column);

Solution

1. Step 1: Recall SQL syntax for indexes

   Secondary indexes are created using the standard CREATE INDEX statement without the PRIMARY keyword.

2. Step 2: Identify the correct syntax

   CREATE INDEX idx_name ON table(column); uses the correct syntax: CREATE INDEX idx_name ON table(column);

3. Final Answer:

   CREATE INDEX idx_name ON table(column); -> Option A

4. Quick Check:

   Secondary index syntax = CREATE INDEX [OK]

Hint: Secondary index uses CREATE INDEX without PRIMARY [OK]

Common Mistakes:

• Using CREATE SECONDARY INDEX which is invalid
• Confusing with CREATE PRIMARY INDEX syntax
• Using UNIQUE keyword incorrectly for secondary index

3. Consider a table Employees(emp_id, name, department) where emp_id is the primary key. Which index type would speed up a query filtering by department?

medium

A. Primary index on department

B. Primary index on emp_id

C. Secondary index on department

D. No index needed

Solution

1. Step 1: Identify the primary key and its index

   The primary key is emp_id, so the primary index is on emp_id.

2. Step 2: Determine which index helps filter by department

   Since department is not the primary key, a secondary index on department speeds up queries filtering by it.

3. Final Answer:

   Secondary index on department -> Option C

4. Quick Check:

   Filter by non-key column = secondary index [OK]

Hint: Use secondary index for non-primary key columns [OK]

Common Mistakes:

• Assuming primary index helps filter by any column
• Trying to create primary index on non-key column
• Ignoring the benefit of secondary indexes

4. A developer created a secondary index on a column that contains many duplicate values. What is the likely problem?

medium

A. The database will reject the index creation

B. The primary index will be corrupted

C. The secondary index will enforce uniqueness

D. The secondary index will be inefficient due to low uniqueness

Solution

1. Step 1: Understand secondary index behavior with duplicates

   Secondary indexes can be created on columns with duplicates but may become less efficient because many records share the same key.

2. Step 2: Identify the impact on performance

   Low uniqueness means the index has many entries pointing to multiple rows, slowing down search performance.

3. Final Answer:

   The secondary index will be inefficient due to low uniqueness -> Option D

4. Quick Check:

   Duplicates in secondary index = inefficiency [OK]

Hint: Secondary index on duplicates slows searches [OK]

Common Mistakes:

• Thinking secondary index enforces uniqueness
• Believing primary index gets corrupted
• Expecting index creation to fail

5. You have a large table with a primary index on customer_id and a secondary index on city. You want to optimize queries filtering by both customer_id and city. What is the best indexing strategy?

hard

A. Drop the secondary index and rely only on primary index

B. Create a composite index on (customer_id, city)

C. Create a secondary index on customer_id only

D. Create two separate secondary indexes on customer_id and city

Solution

1. Step 1: Analyze current indexes and query filters

   Primary index exists on customer_id, secondary index on city. Queries filter by both columns.

2. Step 2: Understand composite index benefits

   A composite index on (customer_id, city) allows efficient filtering on both columns together, improving query speed.

3. Step 3: Evaluate other options

   Dropping indexes or creating separate secondary indexes won't optimize combined filtering as well as a composite index.

4. Final Answer:

   Create a composite index on (customer_id, city) -> Option B

5. Quick Check:

   Combined filter = composite index [OK]

Hint: Use composite index for multi-column filters [OK]

Common Mistakes:

• Dropping useful indexes
• Creating redundant secondary indexes
• Ignoring composite index advantages