Skip to content
OrbixPanel OrbixPanel Tutorials

Speed up a slow database with indexes

When a page that touches the database is slow, the cause is usually a query scanning a whole table because it’s missing an index. Adding the right index can turn a multi-second query into a sub-millisecond one.

What an index is

An index is like the index at the back of a book: instead of reading every page to find a word, the database jumps straight to the rows that match. You add indexes on the columns you filter, join, or sort by.

Find the queries that need one

MySQL — run the slow query with EXPLAIN in phpMyAdmin or the terminal:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

If you see type: ALL and a big rows count, it’s scanning the whole table — index the column in the WHERE.

PostgreSQL:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

A Seq Scan on a large table is the red flag.

Add the index

-- index a single filtered column
CREATE INDEX idx_orders_customer ON orders (customer_id);


-- composite index for a filter + sort
CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);

Re-run EXPLAIN — you should now see an index scan and far fewer rows examined.

Rules of thumb

  • Index what you query, not every column. Each index speeds reads but slightly slows writes and uses disk.
  • Composite order matters — put the most selective / equality column first.
  • Foreign keys used in joins should almost always be indexed.
  • Most frameworks/ORMs let you declare indexes in a migration — do it there so it’s versioned.

Still slow after indexing?

See find and fix slow queries and consider caching with Redis for repeated reads.

Running OrbixPanel? phpMyAdmin and local DB access make EXPLAIN a two-minute check. OrbixPanel.com → — the free, self-hosted, AI-powered hosting control panel.