Query optimization strategies in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding the Role of Indexes in Query Optimization

Which of the following best explains how indexes improve query performance in a database?

A Indexes store a sorted copy of data that allows faster searching without scanning the entire table.

B Indexes duplicate all data in the table to speed up data retrieval.

C Indexes compress the data to reduce storage space and speed up queries.

D Indexes automatically update the database schema to optimize queries.

💡 Hint

Think about how searching in a phone book is faster when names are sorted alphabetically.

Indexes create a sorted structure that helps the database quickly locate rows without scanning the entire table, improving query speed.

📋 Factual

intermediate

Cost-Based vs Rule-Based Optimization

Which statement correctly distinguishes cost-based query optimization from rule-based optimization?

A Cost-based optimization uses statistics to estimate query costs, while rule-based relies on fixed heuristics.

B Rule-based optimization uses statistics, while cost-based uses fixed rules.

C Both cost-based and rule-based optimization ignore query execution costs.

D Cost-based optimization always chooses the longest execution plan.

💡 Hint

Consider which method uses data about the database to make decisions.

Cost-based optimization evaluates different query plans using statistics to pick the cheapest, while rule-based uses predefined rules without cost evaluation.

🔍 Analysis

advanced

Analyzing Query Execution Plans

Given a query execution plan showing a full table scan and an index scan, which strategy is generally more efficient and why?

A Full table scan is more efficient because indexes slow down queries.

B Full table scan is more efficient because it reads all rows sequentially, which is always faster.

C Both scans have the same efficiency regardless of data size.

D Index scan is more efficient because it reads fewer rows by using the index to locate data quickly.

💡 Hint

Think about how looking up a word in a dictionary compares to reading every page.

An index scan uses the index to quickly find relevant rows, reducing the number of rows read compared to a full table scan, which reads every row.

❓ Comparison

advanced

Comparing Join Strategies in Query Optimization

Which join strategy is best suited for joining a small table with a large table when the small table fits in memory?

A Cartesian join, because it multiplies all rows from both tables.

B Hash join, because it always scans both tables fully regardless of size.

C Nested loop join, because it efficiently uses the small table in memory to find matching rows.

D Merge join, because it requires both tables to be sorted and large.

💡 Hint

Consider which join uses one table repeatedly to find matches in the other.

Nested loop join is efficient when the smaller table fits in memory, allowing quick lookups for each row in the larger table.

❓ Reasoning

expert

Impact of Statistics on Query Optimization

What is the likely effect on query optimization if the database statistics are outdated or missing?

A The optimizer will always pick the fastest plan regardless of statistics.

B The optimizer may choose inefficient query plans, leading to slower query execution.

C The database will refuse to execute queries without up-to-date statistics.

D Query results will be incorrect due to missing statistics.

💡 Hint

Think about how a GPS might give a bad route if the map is outdated.

Without accurate statistics, the optimizer cannot estimate costs well and may pick slow query plans, but query results remain correct.

Practice

1. What is the main goal of query optimization in a database?

easy

A. To make data retrieval faster and more efficient

B. To increase the size of the database

C. To delete unnecessary data automatically

D. To encrypt data for security

Solution

1. Step 1: Understand the purpose of query optimization

   Query optimization aims to improve how quickly and efficiently data is retrieved from a database.

2. Step 2: Compare options with the goal

   Only To make data retrieval faster and more efficient matches this goal; others describe unrelated tasks.

3. Final Answer:

   To make data retrieval faster and more efficient -> Option A

4. Quick Check:

   Query optimization = faster data retrieval [OK]

Hint: Focus on speed and efficiency of data retrieval [OK]

Common Mistakes:

• Confusing optimization with data deletion
• Thinking optimization increases database size
• Mixing security tasks with optimization

2. Which of the following is a correct SQL syntax to create an index on the column employee_id in the table employees?

easy

A. CREATE employees INDEX idx_emp_id(employee_id);

B. MAKE INDEX idx_emp_id IN employees(employee_id);

C. INDEX CREATE idx_emp_id FOR employees(employee_id);

D. CREATE INDEX idx_emp_id ON employees(employee_id);

Solution

1. Step 1: Recall correct SQL syntax for creating an index

   The standard syntax is CREATE INDEX index_name ON table_name(column_name);

2. Step 2: Match options with correct syntax

   Only CREATE INDEX idx_emp_id ON employees(employee_id); matches the correct syntax exactly.

3. Final Answer:

   CREATE INDEX idx_emp_id ON employees(employee_id); -> Option D

4. Quick Check:

   CREATE INDEX ... ON ... (column) [OK]

Hint: Remember: CREATE INDEX index_name ON table(column) [OK]

Common Mistakes:

• Using wrong keywords like MAKE or FOR
• Placing table name before INDEX keyword
• Incorrect order of clauses

3. Consider the SQL query:
SELECT * FROM orders WHERE customer_id = 123;
If there is an index on customer_id, what is the expected effect on query performance?

medium

A. The query will return incorrect results

B. The query will run slower because indexes add overhead

C. The query will run faster by quickly locating matching rows

D. The query will ignore the index and scan the whole table

Solution

1. Step 1: Understand the role of indexes in queries

   Indexes help the database find rows matching conditions faster without scanning the entire table.

2. Step 2: Analyze the effect of an index on customer_id

   Since the query filters by customer_id, the index speeds up locating those rows.

3. Final Answer:

   The query will run faster by quickly locating matching rows -> Option C

4. Quick Check:

   Index on filter column = faster query [OK]

Hint: Index on filter column speeds up data retrieval [OK]

Common Mistakes:

• Thinking indexes slow down SELECT queries
• Assuming indexes cause wrong results
• Believing indexes are always ignored

4. A developer wrote this SQL query:
SELECT * FROM products WHERE price > 100 AND price < 50;
What is the main issue affecting query optimization here?

medium

A. The SELECT * syntax is invalid

B. The WHERE clause has conflicting conditions making the query return no rows

C. The query is missing an index on the price column

D. The table name is misspelled

Solution

1. Step 1: Analyze the WHERE clause conditions

   The conditions price > 100 and price < 50 cannot be true at the same time.

2. Step 2: Understand impact on query results and optimization

   This conflict means no rows will match, so the query returns empty results, wasting resources.

3. Final Answer:

   The WHERE clause has conflicting conditions making the query return no rows -> Option B

4. Quick Check:

   Conflicting WHERE conditions = no results [OK]

Hint: Check WHERE conditions for logical conflicts [OK]

Common Mistakes:

• Assuming SELECT * is wrong syntax
• Ignoring logical conflicts in conditions
• Thinking missing index causes no results

5. You have a large table sales with columns sale_date, region, and amount. You want to optimize this query:
SELECT region, SUM(amount) FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31' GROUP BY region;
Which strategy will best improve performance?

hard

A. Create a composite index on (sale_date, region)

B. Create an index only on amount

C. Remove the GROUP BY clause

D. Use SELECT * instead of specific columns

Solution

1. Step 1: Identify columns used in WHERE and GROUP BY clauses

   The query filters by sale_date and groups by region.

2. Step 2: Choose an index that supports both filtering and grouping

   A composite index on (sale_date, region) helps quickly find rows in the date range and group them efficiently.

3. Final Answer:

   Create a composite index on (sale_date, region) -> Option A

4. Quick Check:

   Composite index on filter and group columns = better performance [OK]

Hint: Index columns used in WHERE and GROUP BY together [OK]

Common Mistakes:

• Indexing only the aggregated column
• Removing GROUP BY loses needed grouping
• Using SELECT * increases data load unnecessarily