The 5 database indexes every growing app needs

1. Foreign-key indexes

If you have a column that ends in _id and points at another table, it needs an index. MySQL creates one automatically for the primary key on the parent table, but not for the FK column on the child. Missing FK indexes turn every JOIN into a full table scan — the single most common cause of "the database got slow this month" on a growing app.

2. Filter-column indexes

• status (pending, active, deleted — queried on nearly every list endpoint).
• user_id on any table that belongs to a user.
• created_at or updated_at if you ever ORDER BY or range-filter on dates.
• A composite (user_id, created_at DESC) beats two separate indexes when both columns appear together in the same query.

3. Covering indexes for hot read paths

When one query runs a million times a day, add an index that contains every column the query reads. The database answers from the index alone and never touches the table. One client cut their dashboard load from 2.4s to 180ms with a single covering index on their most-hit endpoint — no application changes required.

4. Unique indexes that do double duty

Unique indexes enforce data integrity (no duplicate emails, no duplicate SKUs) and speed up equality lookups at the same time. Every column you treat as a business identifier — email, username, external ID — should have a UNIQUE constraint, which quietly builds an index underneath. Skip this and you'll eventually write defensive code that could have been a one-line schema change.

5. Partial and full-text indexes

• Partial indexes (PostgreSQL) or filtered indexes (SQL Server) only index rows matching a predicate — e.g., WHERE status = 'active'. Smaller, faster, and cheaper to maintain.
• Full-text indexes (MySQL FULLTEXT, PostgreSQL GIN on tsvector) turn LIKE '%keyword%' queries from seconds into milliseconds.
• For JSONB columns in PostgreSQL, a GIN index on the column makes @> containment queries genuinely fast.
• The rule: if you filter on a condition most of the time, stop indexing the whole table — index just the slice that matters.

Five well-chosen indexes beat a 32-core upgrade every time, for a tenth of the cost.

InfoSpectrum Engineering