SQL
A slow query usually reads too many rows. Your job is to prove what it reads, then reduce the work.
Use EXPLAIN before and after a change. Do not guess from vibes or one lucky run.
In a hackathon project, slow often means a page feels stuck when seed data grows. Your laptop may hide the issue when you have only 20 rows.
Test with enough seed data to make bad queries visible. Even a few thousand rows is useful for a student project.
0 of 8 lessons done · proved by a submitted project with a public repository
Do each one yourself, then tap it to tick it off. The ticks are only a checklist for you: they are not marked or scored.
0 of 5 done
SQLite is common for small local projects. It has EXPLAIN QUERY PLAN, which shows if the database scans a table or uses an index.
This example finds orders for one user. Without an index on user_id, SQLite may scan the whole orders table.
EXPLAIN QUERY PLAN
SELECT id, total_amount, status, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC;
-- Possible output before an index:
-- SCAN orders
-- USE TEMP B-TREE FOR ORDER BY
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);
EXPLAIN QUERY PLAN
SELECT id, total_amount, status, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC;
-- Better output after the index:
-- SEARCH orders USING INDEX idx_orders_user_created (user_id=?)SCAN means the database may read the whole table. SEARCH USING INDEX means it can jump closer to the rows it needs.
For this lesson, you do not need to master every planner term. First learn to spot full scans on tables that will grow.
An index helps reads, but it also costs storage and slows inserts or updates a little. Add indexes for real lookup patterns, not every column.
Good index candidates are columns used often in WHERE, JOIN, or ORDER BY. For example: user_id, event_id, created_at, status, or a pair like (user_id, created_at).
Recruiters cannot see your thinking unless you leave evidence. Add a short performance note to your README or docs folder.
Keep it simple. Show the query, the problem, the index, and the changed plan.
Performance note: orders list
Feature: Show a user's recent orders.
Problem: Query scanned the full orders table.
Fix: Added idx_orders_user_created on orders(user_id, created_at DESC).
Before plan: SCAN orders, USE TEMP B-TREE FOR ORDER BY.
After plan: SEARCH orders USING INDEX idx_orders_user_created (user_id=?).Do each one yourself, then tap it to tick it off. The ticks are only a checklist for you: they are not marked or scored.
0 of 5 done
Answer the quick check to finish this lesson.