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.