Database Indexing: When to Create and When Not To

Your query takes eight seconds on a table with two million rows, and the page times out before it shows anything. You open the MySQL log, run the query through EXPLAIN, and see ALL in the type column; meaning the engine is reading the entire table. Now the real question is: which column should you index, and should you even create one at all?

A database index is a sorted structure that MySQL keeps alongside the main data so it can reach rows without scanning the entire table. But every index is a second table that must be updated on every INSERT, UPDATE, and DELETE. That's where the equation falls apart.

Why EXPLAIN Shows type=ALL Means the Table Is Being Scanned

When the WHERE condition is on a column that has no index, MySQL has no choice but to read every row. See this with a simple example:

EXPLAIN SELECT id, title FROM posts WHERE author_id = 42;
-- type: ALL, rows: 1843200, key: NULL

The rows number is an estimate, not exact, but when it reaches into the millions it means disaster. After creating the index:

CREATE INDEX idx_author ON posts (author_id);
-- type: ref, rows: 312, key: idx_author

The difference between 1.8 million rows and 312 rows is exactly what the user feels as a fast page load. If you're working with WordPress and author archive pages are slow, you're probably missing exactly this index.

Composite Indexes and the Order Everyone Gets Wrong

A composite index is built on multiple columns, and the order of the columns has real meaning. The rule is simple: read the columns from left to right; MySQL uses the index up to the point where the conditions remain contiguous.

CREATE INDEX idx_status_date ON orders (status, created_at);

This index works for WHERE status = 'paid' and for WHERE status = 'paid' AND created_at > '2024-01-01'. But for WHERE created_at > '2024-01-01' alone, it's useless, because the first column isn't in the condition. This is where people make the mistake: they create a composite index, test the query, see it's still slow, and conclude "indexes don't help." In reality, the index is there — its order is wrong.

The rule for choosing order: the column that appears in an equality condition (=) goes first, the column that appears in a range condition (>, <, BETWEEN) goes after it. ORDER BY columns can also be added at the end so that sorting is done from the index and filesort is eliminated.

The Cost Nobody Accounts For: Write Speed

Every additional index makes every INSERT slower. On a table with five indexes, inserting a row means six disk writes instead of one. If your table is mostly read (like posts or products), this cost is negligible. If the table is write-heavy (logs, sessions, message queues, a high-traffic wp_options table), every additional index is a debt.

In practice, on a log table with 2,000 inserts per second, adding a useless index dropped the insert rate to about 1,400 and the queue deadlocked. The problem wasn't the read query; it was an index that someone had forgotten why they created it.

So the right decision is: create indexes for queries that actually run, not for every column that "might be needed someday." Find unused indexes with sys.schema_unused_indexes:

SELECT * FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('mysql','sys');

When Not to Create an Index

• On low-cardinality columns like is_active that only have two values. An index on such a column is usually rejected because the optimizer realizes reading the index is more expensive than scanning.
• On small tables under a few thousand rows. A full scan of these takes milliseconds.
• On a column that's wrapped in a function in the WHERE clause: WHERE YEAR(created_at) = 2024 renders an index on created_at useless. Rewrite the condition as a range.
• On tables where writes dominate and read queries are rare.

Covering Indexes and the Hidden Cost

A covering index contains all the columns the query needs, so MySQL doesn't touch the main table at all. In EXPLAIN, you see this as Using index in the Extra column. The difference from the normal case can be several times faster, because every lookup to the table is a random disk read.

CREATE INDEX idx_cover ON orders (status, created_at, total);
SELECT status, created_at, total FROM orders WHERE status = 'paid';

But this index is larger and takes up more memory in innodb_buffer_pool_size. If the buffer is small, the covering index falls out of memory and loses its advantage. Here you have a real choice: a narrow index with table lookups, or a wide index with higher memory consumption. For high-traffic tables, I prefer the narrow index unless the query is truly hot.

Practical Method: Measure Before and After

1. Pull the slow query from slow_query_log. Set long_query_time to 1 second and wait a day.
2. Use EXPLAIN to see which step is expensive. If type=ALL, an index is missing.
3. Create the index and re-evaluate the same query with EXPLAIN and EXPLAIN ANALYZE.
4. Compare execution time with SHOW PROFILES or the slow log. Write down the before and after numbers; human memory is weak.
5. A week later, check with schema_unused_indexes that the index is actually being used.

On shared servers, creating an index on a large table takes a lock and takes the site offline for a few minutes. If you have MySQL 5.6 or higher, use ALTER TABLE ... ADD INDEX ... , ALGORITHM=INPLACE, LOCK=NONE so writes aren't blocked. Older versions don't have this option and you'll need to work during low-traffic windows.

If you're working with WordPress and don't want to deal with table locks during peak hours, running these changes on WordPress hosting with a control panel and direct MySQL access makes things easier. For WordPress's recurring queries, before touching the table structure, take a look at WordPress wp-cron; why it doesn't run and how to replace it with real cron; sometimes the slowness isn't from a query, it's from scheduled tasks running back to back.

For a quick check of server status and service responsiveness, free tools from ServerNet are available without installing anything. If you're just getting familiar with table structures and queries, documentation and the knowledge base is a better starting point than trial and error on the production server. And if you're getting connection errors or timeouts, before making any database changes, check HTTP status codes: a practical guide to common errors to make sure the problem is at the database layer and not somewhere else.

Frequently Asked Questions

Does a database index slow down writes?

Yes, and this cost is real. Every index must also be updated on every insert, update, and delete, so the number of disk write operations increases with the number of indexes. On read-heavy tables this cost is negligible, but on write-heavy tables like logs or sessions it can be noticeable.

How important is column order in a composite index?

Very important, and this is where the biggest mistake comes from. The column in the equality condition should come first, and the range column after it. If the order is reversed, MySQL can't use the first part of the index and the query stays slow.

How do I know if an index is unused?

In MySQL 5.7 and higher, the sys.schema_unused_indexes table shows indexes that haven't been used since the last server restart. If an index stays on this list for weeks, it probably only costs you write speed and you can drop it.

Is an index useful on long text columns?

On long VARCHAR or TEXT, a full index becomes bulky and slow. The solution is a prefix index: CREATE INDEX idx_title ON posts (title(20));. This works for LIKE 'foo%' searches but not for LIKE '%foo%'.

Before creating any index, ask one question: which query is supposed to get faster? If the answer isn't clear, don't create the index. An index that nobody has a query for only slows down writes and eats up buffer memory.