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
await db.query('SELECT * FROM orders WHERE customer_id = $1', [customerId]);
// No index on customer_id — full table scan on millions of rowsawait 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 rowsCreating 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