Query optimization strategies in DBMS Theory - Mini Project: Build & Apply

Query Optimization Strategies

📖 Scenario: You are a database administrator who wants to improve the speed of queries on a sales database. You will learn how to organize and optimize queries to get results faster.

🎯 Goal: Build a simple example showing how to set up a table, add an index, write a query, and apply a query hint to optimize performance.

📋 What You'll Learn

Create a table named sales with columns id, product, and amount

Add an index on the product column

Write a query to select all rows where product equals 'Book'

Add a query hint to force index usage in the query

💡 Why This Matters

🌍 Real World

Database administrators and developers often optimize queries to make applications faster and more efficient.

💼 Career

Understanding query optimization is essential for roles like database administrator, backend developer, and data engineer.

1

Create the sales table

Write SQL code to create a table called sales with columns id as integer primary key, product as text, and amount as integer.

DBMS Theory

-- Your code here to create the sales table

Need a hint?

Use CREATE TABLE with the specified columns and types.

2

Add an index on the product column

Write SQL code to create an index named idx_product on the product column of the sales table.

DBMS Theory

CREATE TABLE sales (
  id INT PRIMARY KEY,
  product TEXT,
  amount INT
);
-- Your code here to create the index

Need a hint?

Use CREATE INDEX with the index name and column.

3

Write a query to select books

Write a SQL SELECT statement to get all columns from sales where product equals 'Book'.

DBMS Theory

CREATE TABLE sales (
  id INT PRIMARY KEY,
  product TEXT,
  amount INT
);
CREATE INDEX idx_product ON sales(product);
-- Your code here to select rows where product is 'Book'

Need a hint?

Use SELECT * FROM sales WHERE product = 'Book'.

4

Add a query hint to use the index

Modify the previous SELECT query to include a query hint that forces the use of the idx_product index. Use the syntax USE INDEX (idx_product) after the table name.

DBMS Theory

CREATE TABLE sales (
  id INT PRIMARY KEY,
  product TEXT,
  amount INT
);
CREATE INDEX idx_product ON sales(product);
-- Your code here to add a query hint to use idx_product index
SELECT * FROM sales USE INDEX (idx_product) WHERE product = 'Book';

Need a hint?

Add USE INDEX (idx_product) after the table name in the SELECT statement.