Why optimization prevents slow queries in Supabase - See It in Action

Why optimization prevents slow queries

📖 Scenario: You are managing a database for an online bookstore using Supabase. The database has a table called books with thousands of entries. You notice that searching for books by author is very slow. To fix this, you will learn how to optimize queries by adding an index.

🎯 Goal: Create a table books with sample data, add an index on the author column, and write a query that uses this index to quickly find books by a specific author.

📋 What You'll Learn

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

Insert 3 sample rows into the books table with exact values

Create an index on the author column

Write a SELECT query to find all books by the author 'Jane Austen'

💡 Why This Matters

🌍 Real World

Indexes are used in real databases to make searches fast, especially when tables have many rows.

💼 Career

Database optimization is a key skill for backend developers and cloud engineers to ensure applications run efficiently.

1

Create the books table with sample data

Write SQL to create a table called books with columns id (integer primary key), title (text), and author (text). Then insert these exact rows: (1, 'Pride and Prejudice', 'Jane Austen'), (2, '1984', 'George Orwell'), (3, 'Emma', 'Jane Austen').

Supabase

-- Your code here to create table and insert rows

Hint

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

2

Add an index on the author column

Write SQL to create an index called idx_author on the author column of the books table to speed up searches by author.

Supabase

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

INSERT INTO books (id, title, author) VALUES
  (1, 'Pride and Prejudice', 'Jane Austen'),
  (2, '1984', 'George Orwell'),
  (3, 'Emma', 'Jane Austen');

-- Your code here to create index on author

Hint

Use CREATE INDEX index_name ON table_name(column_name);

3

Write a query to find books by 'Jane Austen'

Write a SELECT query to get all columns from books where the author is exactly 'Jane Austen'.

Supabase

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

INSERT INTO books (id, title, author) VALUES
  (1, 'Pride and Prejudice', 'Jane Austen'),
  (2, '1984', 'George Orwell'),
  (3, 'Emma', 'Jane Austen');

CREATE INDEX idx_author ON books(author);

-- Your code here to select books by Jane Austen

Hint

Use SELECT * FROM books WHERE author = 'Jane Austen';

4

Explain how the index helps prevent slow queries

Add a SQL comment explaining that the index idx_author helps the database find rows with author 'Jane Austen' faster by avoiding scanning the whole table.

Supabase

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

INSERT INTO books (id, title, author) VALUES
  (1, 'Pride and Prejudice', 'Jane Austen'),
  (2, '1984', 'George Orwell'),
  (3, 'Emma', 'Jane Austen');

CREATE INDEX idx_author ON books(author);

SELECT * FROM books WHERE author = 'Jane Austen';

-- Your comment here explaining the index benefit

Hint

Write a comment starting with -- explaining the index speeds up searches by avoiding full table scans.

Practice

1. Why is optimization important for database queries in Supabase?

easy

A. It deletes unnecessary tables automatically.

B. It helps queries run faster by finding data quickly.

C. It makes the database store more data.

D. It changes the database type to NoSQL.

Solution

1. Step 1: Understand query speed

   Queries that are not optimized take longer to find data, causing delays.

2. Step 2: Role of optimization

   Optimization helps the database find data faster, improving query speed.

3. Final Answer:

   It helps queries run faster by finding data quickly. -> Option B

4. Quick Check:

   Optimization = faster queries [OK]

Hint: Optimization means faster data finding [OK]

Common Mistakes:

• Thinking optimization stores more data
• Believing optimization deletes tables
• Confusing optimization with changing database type

2. Which of the following is the correct way to add an index in Supabase SQL to speed up queries on the column user_id?

easy

A. CREATE INDEX ON users (user_id);

B. ADD INDEX users user_id;

C. INDEX CREATE users.user_id;

D. MAKE INDEX users ON user_id;

Solution

1. Step 1: Recall SQL syntax for index creation

   The correct SQL command to create an index is CREATE INDEX index_name ON table_name(column_name); but index_name can be omitted for default naming.

2. Step 2: Match syntax to options

   CREATE INDEX ON users (user_id); uses the correct syntax: CREATE INDEX ON users (user_id); which is valid in PostgreSQL (used by Supabase).

3. Final Answer:

   CREATE INDEX ON users (user_id); -> Option A

4. Quick Check:

   Correct SQL syntax = CREATE INDEX ON users (user_id); [OK]

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

Common Mistakes:

• Using ADD INDEX which is MySQL syntax
• Incorrect order of keywords
• Missing parentheses around column name

3. Given this Supabase SQL query:

SELECT * FROM orders WHERE customer_id = 123;

and an index exists on customer_id, what is the expected effect on query performance?

medium

A. The query will cause an error if an index exists.

B. The query will run slower because indexes add overhead.

C. The query will return no results because indexes filter data.

D. The query will run faster because the index helps find rows quickly.

Solution

1. Step 1: Understand index purpose

   An index on customer_id helps the database find matching rows faster without scanning the whole table.

2. Step 2: Effect on query

   Because the query filters by customer_id, the index speeds up the search, improving performance.

3. Final Answer:

   The query will run faster because the index helps find rows quickly. -> Option D

4. Quick Check:

   Index on filter column = faster query [OK]

Hint: Index on filter column speeds query [OK]

Common Mistakes:

• Thinking indexes slow down SELECT queries
• Believing indexes filter out data
• Assuming indexes cause errors

4. You run EXPLAIN ANALYZE SELECT * FROM products WHERE category = 'books'; in Supabase and notice the query is slow. Which of these is the best fix?

medium

A. Restart the Supabase server.

B. Delete the products table and recreate it.

C. Add an index on the category column.

D. Change the query to SELECT all columns without WHERE.

Solution

1. Step 1: Analyze slow query cause

   The slow query filters by category but likely scans the whole table because no index exists.

2. Step 2: Apply optimization fix

   Adding an index on category lets the database quickly find matching rows, speeding up the query.

3. Final Answer:

   Add an index on the category column. -> Option C

4. Quick Check:

   Index on filter column fixes slow query [OK]

Hint: Add index on slow filter column [OK]

Common Mistakes:

• Deleting tables unnecessarily
• Removing WHERE clause which slows queries
• Restarting server without fixing query

5. You have a large sales table with millions of rows. Queries filtering by region are slow. You add an index on region but queries remain slow. What is the best next step to optimize queries in Supabase?

hard

A. Use EXPLAIN ANALYZE to check if the index is used and adjust the query or index accordingly.

B. Drop the index because it does not help and slows inserts.

C. Add indexes on every column in the table.

D. Switch the database to a NoSQL system.

Solution

1. Step 1: Verify index usage with EXPLAIN ANALYZE

   Using EXPLAIN ANALYZE shows if the query planner uses the index or not, revealing why queries remain slow.

2. Step 2: Adjust query or index based on analysis

   If the index is not used, you may need to rewrite the query or create a different type of index to improve performance.

3. Final Answer:

   Use EXPLAIN ANALYZE to check if the index is used and adjust the query or index accordingly. -> Option A

4. Quick Check:

   Check index use before changing strategy [OK]

Hint: Check index use with EXPLAIN ANALYZE [OK]

Common Mistakes:

• Dropping useful indexes too soon
• Adding too many indexes causing overhead
• Switching database without analysis