Skip to content

Missing index causes slow query

Quick summary

Your query filters on a column with no index — the database does a full table scan, which is slow on large tables.

Why this happens

  • A WHERE clause filters on a column that has no index, causing a sequential scan

  • A join column lacks an index, forcing a nested loop scan of the joined table

  • An index was dropped or never created for a column that is frequently queried

Minimal example

✗ Broken code
await db.query('SELECT * FROM orders WHERE customer_id = $1', [customerId]);
// No index on customer_id — full table scan on millions of rows
✓ Fixed code
await db.query('CREATE INDEX idx_orders_customer ON orders(customer_id)');
await db.query('SELECT * FROM orders WHERE customer_id = $1', [customerId]);
// Now uses index scan — fast even on millions of rows

Creating an index on the filtered column lets the database find matching rows directly instead of scanning the entire table.

How to diagnose

  • Run EXPLAIN ANALYZE on the query and look for Seq Scan or high cost values

  • Check the table indexes with SHOW INDEXES or information_schema

  • Compare query execution time with and without an index on the filtered column

How to fix

  • Create an index on the frequently filtered column: CREATE INDEX idx_name ON table(column)

  • Create a composite index for multi-column WHERE clauses: CREATE INDEX ON table(col1, col2)

  • Add indexes on join columns and ORDER BY columns used in frequent queries

How to prevent

  • Create indexes on columns frequently used in WHERE, JOIN, and ORDER BY clauses

  • Monitor slow query logs and add indexes for queries that show sequential scans

Related Resources

Related Glossary

Related Lessons

← Back to language