SQL Query Analyzer

Tables: orders, customers, order_items

• warningSELECT * returns every column

  Wildcard projections read and transfer columns the application may never use, and they break silently when the table changes.

  → List only the columns you need: SELECT id, name, created_at …

• warningLeading-wildcard LIKE

  A pattern that starts with % cannot use a B-tree index, so it forces a full scan.

  → Anchor the pattern (LIKE 'abc%'), or use a full-text / trigram index.

• warningFunction applied to a filtered column

  Wrapping a column in a function makes the predicate non-sargable, so the index on that column is ignored.

  → Rewrite the predicate (e.g. col >= '2026-01-01' AND col < '2026-02-01') or add a functional/expression index.

• infoORDER BY without LIMIT

  Sorting the full result set costs memory and may spill to disk.

  → Add LIMIT/OFFSET, or sort in the application when the set is small.

• warningLarge OFFSET pagination

  OFFSET N still reads and discards N rows on every page.

  → Use keyset pagination: WHERE id > :last_id ORDER BY id LIMIT :n.

CREATE INDEX idx_o_status ON o (status);
CREATE INDEX idx_c_id ON c (id);
CREATE INDEX idx_o_customer_id ON o (customer_id);
CREATE INDEX idx_i_order_id ON i (order_id);
CREATE INDEX idx_o_id ON o (id);