Primary vs secondary indexes in DBMS Theory - Practice Questions

Challenge - 5 Problems

🧠 Conceptual

intermediate

Difference in uniqueness between primary and secondary indexes

Which statement correctly describes the uniqueness property of primary and secondary indexes in a database?

A Neither primary nor secondary indexes enforce uniqueness on their indexed columns.

B Secondary indexes enforce uniqueness on the indexed column, while primary indexes do not necessarily enforce uniqueness.

C Both primary and secondary indexes always enforce uniqueness on their indexed columns.

D Primary indexes enforce uniqueness on the indexed column, while secondary indexes do not necessarily enforce uniqueness.

💡 Hint

Think about the role of a primary key in a table.

A primary index is built on the primary key, which uniquely identifies each record, so it enforces uniqueness. Secondary indexes can be built on non-unique columns and do not enforce uniqueness.

📋 Factual

intermediate

Storage location of primary vs secondary indexes

Where are primary and secondary indexes typically stored in relation to the data in a database?

A Both primary and secondary indexes store data sorted with the table rows.

B Secondary indexes store data sorted with the table rows; primary indexes store pointers to the data rows separately.

C Primary indexes store data sorted with the table rows; secondary indexes store pointers to the data rows separately.

D Both primary and secondary indexes store pointers to data rows separately.

💡 Hint

Consider how clustered and non-clustered indexes work.

Primary indexes are often clustered, meaning the data is physically sorted according to the primary key. Secondary indexes are non-clustered and store pointers to the actual data rows.

🚀 Application

advanced

Choosing index type for query optimization

You have a table with millions of records and frequent queries filtering by a non-unique column. Which index type should you create to improve query speed without affecting data uniqueness?

A Create a secondary index on the non-unique column.

B Create a primary index on the non-unique column.

C Create a primary index on a unique column and no secondary index.

D Create no index to avoid overhead.

💡 Hint

Primary indexes require uniqueness; think about which index supports non-unique columns.

Primary indexes require unique columns. For non-unique columns, secondary indexes are used to speed up queries without enforcing uniqueness.

🔍 Analysis

advanced

Impact of primary vs secondary indexes on insert performance

How does having a primary index compared to multiple secondary indexes affect the performance of inserting new records into a database table?

A Secondary indexes slow down inserts more than primary indexes because each index must be updated separately.

B Indexes do not affect insert performance.

C Primary and secondary indexes have the same impact on insert performance.

D Primary indexes slow down inserts more than secondary indexes because they require data sorting.

💡 Hint

Consider how many indexes need updating when inserting a new row.

While primary indexes require maintaining sorted data, secondary indexes require updating multiple separate index structures, which can slow inserts more as the number of secondary indexes grows.

❓ Reasoning

expert

Why secondary indexes can cause inconsistent query results if not maintained properly

In a database system, if secondary indexes are not updated correctly after data changes, what kind of problem can occur when querying using those indexes?

A Queries will always fail with a syntax error due to index corruption.

B Queries may return outdated or incorrect results because the secondary index points to wrong or missing data rows.

C Queries will ignore the secondary index and always perform full table scans.

D Queries will return duplicate rows because secondary indexes enforce uniqueness incorrectly.

💡 Hint

Think about what happens if an index points to data that no longer exists or has changed.

If secondary indexes are not updated after data changes, they can point to wrong or missing rows, causing queries to return outdated or incorrect results.

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