Covering index concept in SQL - Mini Project: Build & Apply

Using a Covering Index to Speed Up Queries

📖 Scenario: You manage a small online bookstore database. Customers often search for books by their title and want to see the author and price quickly. To make these searches faster, you will create a special database index called a covering index that includes all the columns needed for the search.

🎯 Goal: Build a covering index on the books table that includes the title, author, and price columns. Then write a query that uses this index to quickly find books by title and show their author and price.

📋 What You'll Learn

Create a books table with columns id, title, author, and price

Insert sample data into the books table

Create a covering index on title including author and price

Write a SELECT query to find books by title showing author and price

💡 Why This Matters

🌍 Real World

Covering indexes are used in real databases to speed up searches by including all needed columns in the index, reducing the time to get results.

💼 Career

Database administrators and developers use covering indexes to optimize query performance and improve user experience in applications.

1

Create the books table and insert data

Create a table called books with columns id (integer primary key), title (text), author (text), and price (decimal). Then insert these three rows exactly: (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99), (2, '1984', 'George Orwell', 8.99), (3, 'To Kill a Mockingbird', 'Harper Lee', 12.99).

SQL

-- Create the books table and insert data
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Create a covering index on title including author and price

Create an index called idx_books_title_covering on the books table that uses title as the indexed column and includes author and price as covering columns.

SQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    price DECIMAL
);

INSERT INTO books (id, title, author, price) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99),
(2, '1984', 'George Orwell', 8.99),
(3, 'To Kill a Mockingbird', 'Harper Lee', 12.99);

-- Create the covering index here
-- Your code here

Hint

Use CREATE INDEX with INCLUDE to add covering columns.

3

Write a SELECT query to find books by title

Write a SELECT query that finds all columns title, author, and price from the books table where the title is exactly '1984'.

SQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    price DECIMAL
);

INSERT INTO books (id, title, author, price) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99),
(2, '1984', 'George Orwell', 8.99),
(3, 'To Kill a Mockingbird', 'Harper Lee', 12.99);

CREATE INDEX idx_books_title_covering ON books (title) INCLUDE (author, price);

-- Write the SELECT query here
-- Your code here

Hint

Use SELECT with WHERE to filter by title.

4

Explain how the covering index helps the query

Add a SQL comment explaining that the index idx_books_title_covering helps the query by allowing the database to find the title and get author and price from the index without reading the full table.

SQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    price DECIMAL
);

INSERT INTO books (id, title, author, price) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99),
(2, '1984', 'George Orwell', 8.99),
(3, 'To Kill a Mockingbird', 'Harper Lee', 12.99);

CREATE INDEX idx_books_title_covering ON books (title) INCLUDE (author, price);

SELECT title, author, price FROM books WHERE title = '1984';

-- Add your explanation comment here
-- Your code here

Hint

Write a SQL comment starting with -- explaining the benefit of the covering index.

Practice

1. What is the main benefit of a covering index in a database?

easy

A. It contains all columns needed by a query, avoiding table lookups.

B. It compresses data to save disk space.

C. It automatically backs up the database.

D. It encrypts data for security.

Solution

1. Step 1: Understand what a covering index stores

   A covering index includes all columns that a query needs, so the database can get results from the index alone.

2. Step 2: Identify the benefit of avoiding table lookups

   By not accessing the main table, queries run faster because less data is read and fewer disk operations occur.

3. Final Answer:

   It contains all columns needed by a query, avoiding table lookups. -> Option A

4. Quick Check:

   Covering index = all needed columns [OK]

Hint: Covering index has all query columns, no table needed [OK]

Common Mistakes:

• Thinking it compresses data
• Confusing with backup or encryption
• Assuming it stores only keys

2. Which of the following is the correct syntax to create a covering index on columns name and age in a table users?

easy

A. CREATE INDEX idx_users_name ON users(name, age);

B. CREATE INDEX idx_users_name_age ON users(name, age);

C. CREATE INDEX idx_users ON users WHERE name, age;

D. CREATE INDEX idx_users_name_age ON users USING name, age;

Solution

1. Step 1: Recall the syntax for creating an index on multiple columns

   The correct syntax is CREATE INDEX index_name ON table_name(column1, column2);

2. Step 2: Identify the option matching this syntax with both columns

   CREATE INDEX idx_users_name_age ON users(name, age); correctly names the index and lists both columns in parentheses separated by a comma.

3. Final Answer:

   CREATE INDEX idx_users_name_age ON users(name, age); -> Option B

4. Quick Check:

   CREATE INDEX with columns in parentheses [OK]

Hint: Use parentheses with columns separated by commas [OK]

Common Mistakes:

• Using WHERE instead of column list
• Missing parentheses around columns
• Incorrect keyword usage like USING

3. Given the table orders(order_id, customer_id, order_date, total) and an index on (customer_id, order_date, total), what will the query below use to speed up retrieval?

SELECT customer_id, order_date, total FROM orders WHERE customer_id = 5;

medium

A. The index covers all needed columns, so no table access is needed.

B. The index is ignored because it lacks order_id.

C. The query will scan the entire table ignoring the index.

D. The index is used only for filtering, then table rows are accessed.

Solution

1. Step 1: Check if the index includes all columns in SELECT and WHERE

   The index has customer_id, order_date, and total, which are all requested in SELECT and filtered in WHERE.

2. Step 2: Understand covering index usage

   Since the index has all needed columns, the database can answer the query using only the index without accessing the main table.

3. Final Answer:

   The index covers all needed columns, so no table access is needed. -> Option A

4. Quick Check:

   Index covers SELECT and WHERE columns [OK]

Hint: If index has all SELECT columns, no table needed [OK]

Common Mistakes:

• Thinking order_id is required in SELECT
• Assuming index is ignored if not all table columns included
• Believing table scan always happens

4. You created an index on (product_id, price) but your query SELECT product_id, price, stock FROM products WHERE product_id = 10; is slow. What is the likely reason?

medium

A. The index columns are in wrong order.

B. The index should be on stock only.

C. The query syntax is incorrect.

D. The index is not covering because stock is missing, causing table lookups.

Solution

1. Step 1: Identify columns used in SELECT and index

   The query selects product_id, price, and stock, but the index only has product_id and price.

2. Step 2: Understand why missing columns slow query

   Because stock is missing from the index, the database must access the main table to get stock values, slowing the query.

3. Final Answer:

   The index is not covering because stock is missing, causing table lookups. -> Option D

4. Quick Check:

   Missing SELECT column in index causes table access [OK]

Hint: Covering index must include all SELECT columns [OK]

Common Mistakes:

• Thinking column order affects covering
• Blaming query syntax without error
• Assuming index on stock only helps

5. You want to optimize this query:

SELECT user_id, last_login, status FROM users WHERE status = 'active' AND last_login > '2024-01-01';

Which covering index will best speed up this query?

hard

A. CREATE INDEX idx_users_userid ON users(user_id);

B. CREATE INDEX idx_users_login_status ON users(last_login, status);

C. CREATE INDEX idx_users_status_login ON users(status, last_login, user_id);

D. CREATE INDEX idx_users_status ON users(status);

Solution

1. Step 1: Identify columns used in WHERE and SELECT

   The query filters by status and last_login, and selects user_id, last_login, and status.

2. Step 2: Choose index covering all these columns

   CREATE INDEX idx_users_status_login ON users(status, last_login, user_id); includes status, last_login, and user_id, covering all needed columns for filtering and selection.

3. Step 3: Confirm column order for filtering

   Status is first in the index, matching the equality filter, then last_login for range filter, optimizing index usage.

4. Final Answer:

   CREATE INDEX idx_users_status_login ON users(status, last_login, user_id); -> Option C

5. Quick Check:

   Covering index with WHERE and SELECT columns in filter order [OK]

Hint: Include all WHERE and SELECT columns in index, filter columns first [OK]

Common Mistakes:

• Ignoring SELECT columns in index
• Wrong column order reducing index efficiency
• Creating index on only one filter column