SQL - lesson 6 | HackIndia Skills

Key point

One idea

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.

What slow means

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.

Your to-do

Find the query

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.

Open the page or API route that feels slow.
Copy the exact SQL used by that feature.
Replace user input with safe sample values.
Run the query in your database shell or DB client.
Save the query in a notes file so you can compare later.

0 of 5 done

Example

Use EXPLAIN

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=?)

Tip

Read the plan

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.

Common mistake

Do not index everything

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).