When a site launches, the database holds a few hundred rows and every query is instant. A year or two later the orders, products and customers tables hold hundreds of thousands of rows, and queries nobody noticed before start taking seconds. The cause is usually not a weak server but a missing index. One well-chosen index can make a heavy query several times faster, sometimes hundreds of times.
What an index is
An index works like the alphabetical index at the back of a book: instead of reading every page, you look up the page number and go straight there. Without an index, MySQL checks every row of the table, a full table scan. An index stores column values in an ordered tree, so the server reaches matching rows in a few steps. In InnoDB the primary key is always indexed, so lookups by id are fast; trouble starts with other columns such as phone number, status, category or date.
Which columns need one
Good candidates are columns used in WHERE filters, JOIN conditions and ORDER BY. Find them from the slow query log rather than guessing. Do not index everything: a column with only two or three values, like an active flag, helps little on its own but can be useful inside a composite index.
Seeing the problem with EXPLAIN
EXPLAIN SELECT id, status, total
FROM orders
WHERE phone = '998901234567'
ORDER BY created_at DESC;type = ALL, key = NULL and a rows value close to the table size mean a full scan; Using filesort means an extra sort step.
Adding the index
For a filter plus sort, a composite index works best: the equality column first, then the sort column.
SHOW INDEX FROM orders;
ALTER TABLE orders
ADD INDEX idx_phone_created (phone, created_at),
ALGORITHM=INPLACE, LOCK=NONE;On modern MySQL versions, adding a secondary index to an InnoDB table normally does not block reads and writes. ALGORITHM=INPLACE, LOCK=NONE demands that explicitly, and MySQL returns an error instead of locking if it cannot comply. Building an index on a huge table still takes time and extra disk space, so do it during quiet hours after a backup.
Column order matters
Composite indexes are used from the left. (phone, created_at) helps queries on phone alone and on both columns, but usually not queries on created_at alone.
When an index cannot be used
- Functions on the column, like
WHERE YEAR(created_at) = 2026, disable it. Use a range:created_at >= '2026-01-01' AND created_at < '2027-01-01'. LIKE '%text%'with a leading wildcard cannot use a regular index;LIKE 'text%'can.- Type mismatches, such as comparing a text column to a number, can force MySQL to convert and check every row.
The cost of an index
Each index uses disk space and must be updated on every insert or update, so writes get slightly slower. That is negligible for read-heavy sites but can matter for write-heavy log tables. Review and drop unused or duplicate indexes from time to time, for example an index on (phone) that is already covered by (phone, created_at).
Example: delivery service operators
A delivery service in Andijan, misol.uz, had an operator panel where staff searched orders by phone number. Each search took four to five seconds on a table with 1.5 million rows and no index on phone. After adding the composite index above, rows examined in EXPLAIN fell from millions to a few dozen and searches became instant, with no server changes or plan upgrade. Even on shared hosting you can add indexes to your own database via phpMyAdmin. If you cannot tell which query is slow on sayt.uz hosting, contact support through the cabinet.