Database Indexing Basics: Fix Slow Queries in Small Web Apps

Many small web apps feel fast at launch and slow a year later. Nothing obvious changed in the code, but pages that loaded instantly now take seconds, admin reports time out and the server works harder every month. Very often, the cause is the database: tables have grown from hundreds of rows to hundreds of thousands, and queries that were fine at small scale are now scanning everything.

Indexes are usually the first and cheapest fix. This guide explains what they are, how to find the queries that need them and how to avoid adding indexes that hurt more than they help.

What an Index Actually Does

Think of a database table as a large book without page numbers or an index at the back. To find every order from one customer, the database has to read every row, which is called a full table scan. That is fine for small tables but slow for large ones.

An index is a separate, sorted structure that lets the database jump straight to matching rows, like the index at the back of a book. Most relational databases, including MySQL, PostgreSQL and SQLite, use B-tree indexes by default, which work well for equality matches, ranges and sorting.

Primary keys are indexed automatically. Many frameworks also create indexes for unique columns. Other columns you filter or join on usually need indexes added deliberately.

Find the Slow Queries First

Do not guess. Measure which queries are actually slow before changing anything:

• Enable slow query logging in your database, with a threshold that matches your app, so slow statements are recorded.
• Use your framework's debug tools to see how many queries each page runs and how long they take.
• Watch for N+1 queries: one query to load a list, then one more query per item. Eager loading often fixes this without any index.
• Check application monitoring if you have it, for the endpoints with the highest response times.

If you have not chosen your database yet, our guide to choosing a database compares common options for small apps.

Read the Query Plan With EXPLAIN

Every major database can show how it plans to run a query. Put EXPLAIN in front of a slow query and look for signs of trouble:

1. A full scan of a large table where you expected a lookup.
2. A very large number of rows examined compared with rows returned.
3. Temporary tables or file sorts on big result sets.
4. Joins that scan the second table repeatedly.

After adding an index, run EXPLAIN again to confirm the database actually uses it. Sometimes it does not, because of how the query is written or because the table is small enough that a scan is cheaper.

Rule of thumb: index the columns that appear in your WHERE clauses, JOIN conditions and ORDER BY clauses on large tables, starting with the queries that run most often or hurt most.

Choosing the Right Index

Some practical guidelines that cover most small app needs:

• Foreign keys: columns like customer_id or order_id used in joins are prime candidates.
• Composite indexes: for queries filtering on several columns, such as status and created date, one index on both columns can work better than two separate ones. Column order matters: put the column used for equality first, then ranges or sorting.
• Selective columns: indexes help most on columns with many distinct values. A yes or no column alone often benefits little.
• Avoid functions on indexed columns: wrapping a column in a function in the WHERE clause can stop the index from being used. Search on the stored value or use an expression index where supported.
• Leading wildcards: searches like LIKE '%phone' usually cannot use a normal index. Consider full-text search for that.

The Cost of Too Many Indexes

Indexes are not free. Every insert, update and delete must also update each index on the table, and indexes take disk space and memory. A table with a dozen rarely used indexes can make writes noticeably slower. Review indexes periodically, remove ones that are unused or duplicated, and test changes on a copy of production data before deploying.

Also remember that indexes are only one part of performance. Caching repeated results, as explained in our guide to web caching, and reducing unnecessary queries in your code often deliver equally large improvements.

FAQ

Will adding an index lock my production table?

It can, depending on the database, version and table size. Many modern databases support online or concurrent index creation. Check your database's documentation and run large changes during quiet hours.

Should every column have an index?

No. Index columns that real queries filter, join or sort on. Unnecessary indexes slow down writes and waste resources.

How do I know an index worked?

Compare the query plan and timing before and after on realistic data, and watch your slow query log over the following days.

Most slow small web apps do not need a bigger server; they need a few well-chosen indexes and smarter queries. Measure first, index deliberately and keep checking as your data grows.