Database Indexes Explained for Site Owners Who Aren't DBAs
A database index is a sorted copy of a subset of a table's columns that the database engine uses to find rows without scanning every row in the table. When a query can use an index, the database reads a small, ordered structure instead of walking the entire table, and that is the difference between a site that responds in milliseconds and one that stalls while the database grinds through millions of rows.
How database indexes work under the hood
Think of a table as an unordered pile of records. Without an index, a query like SELECT * FROM users WHERE email = '[email protected]' forces the engine to read every row, compare the email column, and discard the non-matches. That is a full table scan, and its cost grows linearly with the table size. An index on the email column changes the game. The engine builds a separate structure, typically a B-tree, that stores the indexed column values in sorted order along with pointers back to the full rows. A lookup then becomes a binary search through the tree, which takes a number of steps proportional to the logarithm of the row count. For a table with a million rows, a full scan might read a million entries, while an indexed lookup touches around twenty nodes.
Indexes do not come free. Every insert, update, or delete on the indexed table must also update the index structure. If you index too many columns, write-heavy tables slow down because the engine maintains multiple sorted copies. The tradeoff is read speed against write cost, and the right balance depends on your workload. A site that mostly serves content and rarely updates benefits from broad indexing. A site that logs user actions continuously needs to be more conservative.
When to add an index, and how to check
You do not need to guess. The database itself tells you what queries are slow and whether an index exists for them. Start by enabling the slow query log in MySQL or PostgreSQL. In MySQL, you can set it with a config directive in my.cnf:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
That logs every query that takes longer than one second. After a day or two of traffic, inspect the log for the most frequent slow queries. For each one, run EXPLAIN in front of the query to see the execution plan. Look for the type column: if it says ALL, that means a full table scan and you likely need an index. If it says ref or range, the engine is already using an index. The command looks like this:
EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;
The output will show a row with a key column. If that column is NULL, no index is being used. Create one with CREATE INDEX idx_orders_customer ON orders (customer_id); and rerun the EXPLAIN. The key column should now show the index name, and the rows estimate should drop dramatically.
Composite indexes and the leftmost prefix rule
When a query filters on multiple columns, a single-column index may not be enough. Consider SELECT * FROM shipments WHERE status = 'pending' AND warehouse_id = 7. An index on status alone narrows the scan to pending rows, but the engine still has to check each one for the warehouse. A composite index on (status, warehouse_id) lets the engine use both conditions in the tree walk. The order of columns in a composite index matters a lot. The engine can use the index for any query that filters on the leftmost columns in the order you define them. An index on (warehouse_id, status) works for a query filtering on warehouse_id alone, but not for one filtering on status alone. This is the leftmost prefix rule, and it is the most common reason a seemingly good index never gets used.
To find the right column order, look at the WHERE clauses in your slow queries. Put the most selective column first, the one that eliminates the most rows. For example, a status column with three distinct values is not selective, while a customer_id with thousands of distinct values is very selective. If a query filters on both, put customer_id first. But if you also run queries that filter on status alone, you might need a separate single-column index on status.
When to remove an index
Indexes are not permanent. A column that once seemed important may no longer appear in any query, and the index is just overhead on every write. To spot unused indexes, query the database's statistics tables. In MySQL, SHOW INDEX FROM your_table; lists all indexes, but it does not tell you usage. The performance schema does, with a query like SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage WHERE object_schema = 'your_database';. That shows how many times each index was used for reads and writes. An index with a high write count and a near-zero read count is a candidate for removal. Drop it with DROP INDEX index_name ON your_table; and watch the slow query log for a few days to confirm nothing breaks.
Be careful with indexes on very small tables. If a table has fewer than a few thousand rows, a full scan is so fast that an index adds no measurable benefit, and the write overhead is pure waste. Similarly, indexes on columns that are updated frequently, like a last_login timestamp, cause constant index churn. If you rarely query by that column, the index is a liability.
Practical checks before you change anything
Before you create or drop an index, run the actual query with EXPLAIN and note the rows value. After the change, rerun the same EXPLAIN and compare. The number should drop by at least an order of magnitude. Also measure the query time directly with SELECT BENCHMARK(1000, 'your query'); in MySQL, or use EXPLAIN ANALYZE in PostgreSQL to get actual execution times. Do this in a staging environment first, not on production, because a bad index can lock a table during creation on a large dataset. For tables with millions of rows, create the index with ALTER TABLE your_table ADD INDEX idx_name (column), ALGORITHM=INPLACE, LOCK=NONE; in MySQL to avoid blocking writes.
Database indexes explained as a habit, not a one-time fix
Indexing is not a set-and-forget task. Your queries change as your site grows, new features add new filters, and old indexes become dead weight. Make it a routine, every few weeks or after any major feature release, to check the slow query log and the index usage statistics. Keep a small notebook of the top five slow queries and the indexes that fix them. When a new slow query appears, run EXPLAIN before you write any application code to optimize the query. Often, the right index makes a slow query instant, and you never need to touch the application logic at all.
