Database indexes in Supabase - Step-by-Step Execution

Process Flow - Database indexes

Start Query

↓

Check for Index

Yes / No?↓

Use Index

↓

Return Results

↓

End

When a query runs, the database checks if an index exists for the search. If yes, it uses the index to find data fast. If no, it scans the whole table, which is slower.

Execution Sample

Supabase

CREATE INDEX idx_user_email ON users(email);

SELECT * FROM users WHERE email = '[e-mail]';

Create an index on the email column, then query users by email using the index for faster lookup.

Process Table

Step	Action	Index Exists?	Query Method	Result
1	Start query to find user by email	N/A	N/A	Query initiated
2	Check if index on email exists	Yes	Use Index	Index used to find matching rows
3	Retrieve matching rows using index	Yes	Use Index	Rows found quickly
4	Return results to client	Yes	Use Index	Results returned
5	End query	N/A	N/A	Query complete

💡 Query ends after using index to quickly find matching rows instead of scanning whole table

Status Tracker

Variable	Start	After Step 2	After Step 3	Final
index_exists	undefined	true	true	true
query_method	undefined	use index	use index	use index
rows_found	0	0	5	5

Key Moments - 2 Insights

Why does the database check for an index before searching?

The database checks for an index (see Step 2 in execution_table) to decide if it can find data quickly. Using an index avoids scanning the whole table, making queries faster.

What happens if there is no index on the searched column?

If no index exists, the database does a full table scan (not shown here). This means it looks at every row, which is slower than using an index.

Visual Quiz - 3 Questions

Look at the execution_table, what is the query method used at Step 3?

A Full table scan

B No method

C Use index

D Cache lookup

💡 Hint

Check the 'Query Method' column at Step 3 in the execution_table

At which step does the database confirm the index exists?

A Step 2

B Step 4

C Step 1

D Step 5

💡 Hint

Look at the 'Index Exists?' column in the execution_table

If the index did not exist, how would the 'query_method' variable change after Step 2?

A It would be 'use index'

B It would be 'full table scan'

C It would be 'no method'

D It would be 'cache lookup'

💡 Hint

Refer to variable_tracker for 'query_method' and think about what happens if no index is found

Concept Snapshot

Database indexes speed up data searches.
Create an index on columns you query often.
Database checks for index before searching.
If index exists, it uses it to find rows fast.
Without index, it scans the whole table slowly.

Full Transcript

When you run a query to find data, the database first checks if there is an index on the column you are searching. If an index exists, it uses that index to quickly find matching rows instead of scanning the entire table. This makes queries much faster. For example, creating an index on the email column lets the database find users by email quickly. If no index exists, the database must scan every row, which takes more time. This process is shown step-by-step in the execution table and variable tracker.