Database Indexing Explained: Fix Slow Queries

Database Indexing Explained: Fix Slow Queries in Your Student Project

Demo day. Your attendance-management app opens the student list, and the loading spinner spins… and spins. With fifty rows in the table it was instant; with fifty thousand imported records it takes nine seconds. Nothing about your code changed — the database just can't find rows fast enough any more. The fix is an index: a small, purpose-built data structure that lets the database jump straight to the rows it needs instead of reading the whole table.

This guide explains what an index actually is and why it makes queries faster, how to spot a missing index with EXPLAIN, the four places where indexes matter most, how composite indexes and column order work, what indexes cost you (they aren't free), and the five indexing mistakes students make most often. It ends with a twenty-minute workflow you can run on your own project today. Examples use plain SQL that works on PostgreSQL, MySQL and SQLite with minor dialect differences.

What an index actually is

Think of a textbook with no index pages: to find every mention of "photosynthesis" you'd read the whole book. A real index lists each term with its page numbers, so you jump straight there. A database index does the same for a table: without one, the database performs a sequential scan — it reads every row and checks your WHERE condition one by one. With an index on the searched column, it consults a compact, sorted structure (usually a B-tree, the default in PostgreSQL, MySQL and SQLite) and goes directly to the matching rows.

The practical difference: a sequential scan's work grows with the table — twice the rows, roughly twice the time. An index lookup's work barely grows at all. That's why your query was instant at fifty rows and painful at fifty thousand, and why the fix isn't "a faster laptop".

Seeing the problem: EXPLAIN

Before adding anything, look at what the database is actually doing. Prefix your slow query with EXPLAIN:

EXPLAIN SELECT * FROM students WHERE roll_no = 'A123';

On a table with no index, the plan says something like:

Seq Scan on students  (cost=0.00..1234.56 rows=1 width=72)
  Filter: (roll_no = 'A123'::text)

"Seq Scan" is the smoking gun: the database read the entire table to find one row. Now add the index:

CREATE INDEX idx_students_roll_no ON students(roll_no);

Run EXPLAIN again and the plan changes to an Index Scan (or "Index Seek", depending on your database) — the database now looks up A123 in the index and fetches only that row. Exact plan text and timings vary between databases and data volumes; what matters is the change from Seq Scan to Index Scan, and the wall-clock difference you measure on your own data.

The four places indexes matter most

You don't index "the database". You index the columns your queries actually touch:

Columns in WHERE clauses. WHERE customer_id = ? is the single most common case. Foreign-key columns like customer_id, order_id and student_id are looked up constantly — index them.
JOIN columns. A join matches rows between tables on a key; if that key isn't indexed, the database scans one side for every row of the other. Index both sides of frequent joins.
ORDER BY and GROUP BY columns. Sorting fifty thousand rows on every page load is expensive. An index on the sorted column can hand rows over in order.
Columns with UNIQUE constraints. A unique constraint is enforced with a unique index behind the scenes — you get this one automatically, so don't add a second index on the same column.

A concrete recipe. Your orders page runs SELECT * FROM orders WHERE customer_id = 42 and it's slow:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

One line, in a migration (more on that below), and the page load drops from seconds to milliseconds. Name your indexes descriptively — idx_<table>_<column> — because six months later index_7 tells you nothing.

Composite indexes and column order

Sometimes a query filters on two columns at once:

SELECT * FROM students WHERE branch = 'CSE' AND year = 3;

A composite index covers both:

CREATE INDEX idx_students_branch_year ON students(branch, year);

Column order matters, and the rule is the leftmost prefix: this index speeds up queries filtering on branch alone, or on branch + year together — but not queries filtering on year alone. Think of a phone book sorted by (surname, first name): finding all "Sharma"s is fast, finding all "Aarav"s requires scanning everything.

So order the columns by how your queries actually filter: the most selective, most frequently filtered column first. If you also query year alone elsewhere, that needs its own separate index.

Why you can't just index everything: the costs

Indexes are not free, and "index all the columns just in case" is one of the classic student mistakes:

Slower writes. Every INSERT, UPDATE and DELETE must also update every index on the table. A table with twelve indexes writes noticeably slower than one with three. Read-heavy project tables barely notice; write-heavy logging tables feel it.
Disk space. Each index is a copy of (part of) your data in sorted form. On a student project this is trivial; on millions of rows it's a real budget line.
Planner confusion. The query planner chooses which index to use. With overlapping, redundant indexes it occasionally picks badly. Fewer, deliberate indexes keep the planner's job simple.

The rule of thumb: index the columns you query by, and stop there. A handful of well-chosen indexes per table is normal; twenty is a smell.

Five indexing mistakes students make

1. Indexing every column "just in case."
See the costs above. Add indexes in response to slow queries you've actually measured, not columns that might one day be filtered.

2. Wrapping the indexed column in a function.

SELECT * FROM users WHERE LOWER(email) = 'a@b.com';

A plain index on email can't help here — the database must compute LOWER() on every row first. Either drop the function (store emails lowercased) or, on databases that support it, create the index on the expression itself. The general lesson: the index works when the stored value is compared directly.

3. Leading-wildcard LIKE.
WHERE name LIKE 'Sharma%' can use a normal B-tree index (it's a prefix search). WHERE name LIKE '%sharma' cannot — the database can't navigate a sorted structure when the start of the string is unknown. If you need substring search, that's a full-text-search problem, not an indexing problem.

4. Testing on fifty rows and concluding indexes don't matter.
On tiny tables the planner will often ignore your index and scan anyway — correctly, because scanning fifty rows is faster than consulting an index. Indexes prove their worth at realistic volumes. Test with thousands of rows at minimum; generate fake data if you must.

5. Adding the index on localhost and forgetting the deployed database.
The index lives in the database, not in your code. If you ran CREATE INDEX in a console on your laptop, production doesn't have it. Put every index in a migration (or your schema file) so it travels with the code. "Works fast locally, slow on the server" is this mistake wearing a disguise.

A twenty-minute workflow for your project

Find the slow query. Log slow queries, or just time the endpoint you already know is slow. One query at a time.
EXPLAIN it. Confirm it's a sequential scan (or a nested loop over a scan) rather than guessing.
Hypothesise the index. Which column in the WHERE/JOIN/ORDER BY would narrow the search? Write the CREATE INDEX.
Add it as a migration, run it, then EXPLAIN again. Confirm the plan changed to an index scan, and re-time the endpoint.
Sanity-check writes. If the table is write-heavy, make sure inserts didn't get noticeably slower. For a typical student project, they won't.

Measure before and after, every time. "It feels faster" is not a result; two timings are.

FAQ

Do indexes work in SQLite?
Yes — CREATE INDEX works in SQLite exactly as shown here, which makes it great for practising on your Android or desktop app's local database before touching the server one.

Is the primary key already indexed?
Yes. Primary keys (and unique constraints) get an index automatically — never add a manual index on the same column.

How many indexes per table is too many?
There is no fixed number, but a useful ceiling for student projects: if you can't name the query each index serves, you have too many. Three to six deliberate indexes on a busy table is completely ordinary.

Will an index fix my N+1 query problem?
It will make each of the N+1 queries faster, but the real fix is running fewer queries — fetch the related rows in one join or one batched query instead of one per row. Indexing treats the symptom; restructuring treats the cause.

Should I index boolean or low-cardinality columns?
Rarely worth it alone. An index on a column with two possible values narrows fifty thousand rows down to twenty-five thousand — the planner will usually just scan. Composite indexes that include such a column alongside selective ones are the exception.

Do I need to rebuild indexes?
On student-scale databases, no. Large production databases occasionally rebuild or reorganise indexes after heavy churn, but that is well beyond anything your project needs.

Limitations

Indexing speeds up finding rows. It doesn't fix a bad schema, missing pagination (LIMIT/OFFSET), or fetching ten thousand rows to display ten.
All timings in this guide are illustrative — hardware, data volume and database version change the numbers. Your EXPLAIN output and your stopwatch are the authority.
On very small tables the planner may ignore a perfectly good index. That's correct behaviour, not a bug.
This guide covers the B-tree indexes every mainstream relational database uses by default. Specialist index types (full-text, geospatial, hash) solve different problems and deserve their own guide.

Database Indexing Explained: Fix Slow Queries in Your Student Project