Query optimization strategies in DBMS Theory - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is query optimization in databases?

Query optimization is the process of improving the efficiency of a database query to reduce the time and resources needed to retrieve data.

beginner

Name one common strategy used in query optimization.

One common strategy is using indexes to quickly locate data without scanning the entire table.

intermediate

How does rewriting queries help in optimization?

Rewriting queries means changing the query structure to a more efficient form without changing its result, which helps the database run it faster.

intermediate

What role does the query execution plan play in optimization?

The query execution plan shows the steps the database will take to run a query. Optimizers choose the plan that uses the least resources.

advanced

Why is statistics collection important for query optimization?

Statistics provide information about data distribution and size, helping the optimizer make better decisions on how to run queries efficiently.

Which of the following is NOT a query optimization strategy?

A Query rewriting

B Increasing data redundancy

C Using indexes

D Collecting statistics

Increasing data redundancy is not a query optimization strategy; it can actually slow down queries.

What does a query execution plan help with?

A Showing how the database will execute a query

B Backing up the database

C Encrypting data

D Creating new tables

A query execution plan shows the steps the database will take to run a query.

Why are indexes useful in query optimization?

A They store backup copies of data

B They delete duplicate rows

C They speed up data retrieval by avoiding full table scans

D They compress data

Indexes help speed up data retrieval by allowing the database to find data quickly without scanning the whole table.

What is the purpose of collecting statistics in a database?

A To help the optimizer choose the best query plan

B To increase storage space

C To prevent data loss

D To create user accounts

Statistics help the optimizer understand data distribution and size to choose the best way to run queries.

Which action can improve query performance by changing the query without altering its result?

A Data deletion

B Backup scheduling

C Index dropping

D Query rewriting

Query rewriting changes the query structure to make it run faster while keeping the same results.

Explain the main strategies used in query optimization and why they are important.

Think about how databases find data faster and use less resources.

Describe how the query optimizer uses statistics to improve query performance.

Consider what information helps the optimizer pick the best way to run a query.