Database Indexes in Practice: When They Make Queries 100x Faster, and When They Hurt

An index is the most important performance tool you own in a database, and used correctly it turns a query that scans a million rows into a search that finishes in about 20 steps. The reason is mathematical: an index is a tree structure (B-Tree) with logarithmic depth, so scanning a table of a million rows costs close to a million read operations, while searching a B-Tree index over the same million costs roughly log₂(1,000,000) which is about 20 steps only. That is the difference between a query in 800 milliseconds and one under 5 milliseconds on the same data. But an index is not free: each index you add slows INSERT, UPDATE, and DELETE because the database updates the index on every write, it takes extra disk space, and it can consume memory you need elsewhere. The direct answer: index the columns that appear often in WHERE, JOIN, and ORDER BY, but do not index every column, and always verify with EXPLAIN that the index is actually used before assuming it works. Many indexes get created and then never fire because of how the query itself is written.

The golden rule: an index helps reads and costs writes, so choose based on the read-to-write ratio of your table. A reporting table read thousands of times and written rarely deserves many indexes, while a log table written constantly and read little wants few carefully chosen indexes.

Read EXPLAIN before any decision

Do not guess a query's performance, ask the database itself with EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL, which actually executes and gives real timing). Take a real example: an orders table with a million rows, and the query:

• SELECT * FROM orders WHERE customer_id = 4820;

Before the index, EXPLAIN shows access type ALL in MySQL (a Seq Scan in PostgreSQL) and a rows column near 1,000,000, meaning the database reads every row to filter a few. Execution time is close to 700 to 900 milliseconds on a typical SSD.

Add the index: CREATE INDEX idx_orders_customer ON orders(customer_id);. Now EXPLAIN shows access type ref and a rows column of just a few rows (this customer's order count), and execution time drops to about 2 to 5 milliseconds. That is more than a 100x improvement from one line.

The composite index and column order

When your query filters on more than one column, a composite index over several columns is stronger than separate single indexes. But the order of columns inside it is decisive, and the rule is called the leftmost prefix: an index on (a, b, c) serves queries filtering on a alone, a and b, or a and b and c, but it does not serve a query filtering on b alone or c alone.

Example: an index on (status, created_at) greatly speeds up a query for pending orders sorted by date, but it is useless for a query filtering by date only. The practical rule for column order: put the equality column (=) before the range column (>, <, BETWEEN), and put the most selective column (the one that narrows results most) first when you can. A status column with three values is less selective than a customer_id column with millions of values.

The covering index

If the index contains every column the query needs (in SELECT and WHERE), the database reads the index alone without returning to the table, and this is called a covering index. In MySQL EXPLAIN it shows Using index in the Extra column, and it is among the fastest read patterns there is.

The cost of an index on writes and disk

Do not add indexes without accounting. Each extra index means:

• Slower writes: every INSERT or UPDATE on an indexed column must update the index tree too. A table with five indexes can be 30% to 50% slower to write than the same table with one index.
• Disk space: an index is a sorted copy of its column, and combined indexes can reach a size near or beyond the data itself in heavily indexed tables.
• Memory: the database loads active indexes into memory (the buffer pool) to speed them up, and every index competes with the others over that limited memory.

Indexes that never fire no matter what

The most common cause of a slow "indexed" query is that the index is never used because of how the condition is written. Watch for these traps:

• A function on the indexed column: a condition like WHERE YEAR(created_at) = 2026 disables the index because the database computes the function for every row. The fix: WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01', which keeps the index effective.
• LIKE with a free prefix: WHERE name LIKE '%khalid%' blocks the index because the search does not start from the value's beginning. But LIKE 'khalid%' uses the index since it is a fixed prefix. For free text search use a full-text index (FULLTEXT).
• Type mismatch: comparing a numeric column to a string value forces an implicit conversion that sometimes cancels the index.
• OR across different columns: the planner may fail to use a single index, and sometimes a UNION is faster.
• Weak selectivity: an index on a column with very few values (like is_active with two values) may be ignored by the planner because a table scan is cheaper than reading half the index then returning to the table.

Memory limits across VPS plans

Index performance ties directly to available memory because the database wants to keep active indexes in the buffer pool. Here is a practical guide on cloud server plans:

If your queries suddenly slow down despite the indexes, the index size has likely exceeded memory so the database now reads from disk. The fix is either better indexes or more memory, and adding +2GB RAM for 600 SAR per year is a direct, cheap fix compared to the impact of slowness on user experience. See the pricing and plans for all add-ons.

Index types and when to use each

Not every index is a B-Tree. Choosing the right type for the case saves memory and speeds up the matching kind of search:

A practical example: a JSONB column storing product attributes, where searching inside it with a B-Tree index is useless, while a GIN index makes a query like "all products with attribute color=red" fast. And for free Arabic text search, a FULLTEXT index far outperforms LIKE because it builds a word dictionary in advance.

When to drop an index

Indexes accumulate over time, and some were created for a query that no longer exists. An unused index is pure overhead: it slows writes and eats disk and memory for nothing. Monitor usage periodically: in PostgreSQL, pg_stat_user_indexes gives you the idx_scan column (how many times the index was used), and if it stays zero for a long period it is a drop candidate. In MySQL, sys.schema_unused_indexes gives you a ready list of indexes not called since the last restart. Drop carefully after confirming the related query truly disappeared, since dropping a used index returns a query to a full scan and reverses the improvement. Also watch for redundant indexes: an index on (a) plus an index on (a, b) usually makes the first redundant, because the composite serves the prefix a alone.

Practical conclusion

Start by measuring, not guessing: run EXPLAIN on your three slowest queries and look for access type ALL or Seq Scan, which is the sign of a full scan. Add an index on the most frequent WHERE and JOIN columns, and re-run EXPLAIN to confirm the access type improved to ref or range. Review your indexes periodically and drop unused ones (PostgreSQL gives you pg_stat_user_indexes, and MySQL gives you sys.schema_unused_indexes). The final rule: an index is an engineering decision on the read-versus-write balance, not a magic button you press on every column.