What Is a Database Index? How Indexing Speeds Up Queries

What is a database index? It is a separate lookup structure that helps a database find rows without scanning the whole table. The right index can cut unnecessary reads, but it also consumes storage and adds work to writes. The reliable test is a comparison of real execution plans and row counts.

What is a database index, and what does it do?

A database index is a separate data structure that helps the database find rows without checking the whole table every time. I think of it as the index at the back of a book: useful when the question matches the way the index is organized, not a magic shortcut for every query.

The trade-off is real. An index consumes disk space, uses memory, and adds work to inserts, updates, and deletes. MySQL 8.0 documents indexes as useful for operations involving WHERE, JOIN, ORDER BY, and some GROUP BY clauses, but adding an index to every column is not a plan.

Imagine a 10-million-row orders table with no index on customer_id. To find one customer’s orders, the database may need to inspect a large number of rows. An appropriate index can narrow that search. I do not call the change successful until I have checked the query plan and the rows read with EXPLAIN.

What to do – Inspect the slow query with EXPLAIN before creating an index, and check whether the current plan is scanning more data than necessary.

How an index reduces the work

Take a simple lookup. Without a suitable index, the database may read rows from beginning to end until it finds the matches. That is a full table scan, and the amount of work usually grows with the table.

B-tree indexes are common in relational databases. Their values are arranged in a balanced tree, allowing the database to follow a relevant branch instead of checking every row individually. This can help with equality searches, ranges, and some ordering operations. The exact plan still depends on the database engine and the data.

Here is a small example:

SELECT id, email
FROM users
WHERE email = 'ayse@example.com';

If users.email has a suitable index, the database can locate the value in the index and then visit the matching row. Without one, more rows may be read as the table grows. The improvement does not come from the CPU suddenly becoming clever; it comes from avoiding unnecessary disk and memory reads.

That difference can be noticeable on a VPS. If CPU usage is modest but disk wait is high, a query may be reading more data than it needs. I usually look at iostat, the database slow query log, and EXPLAIN together. One graph rarely tells the whole story.

What to do – Compare rows examined and rows returned, as well as elapsed time, when checking whether an index helped.

Why the optimizer may ignore your index

The optimizer does not have to choose an index. On a small table, reading every row can be cheaper than reading an index and then visiting the table. An index also loses much of its advantage when the query returns a large part of the table.

Column selectivity matters. A status column with only two possible values has low selectivity, even when the table contains millions of rows. If WHERE status = 'active' returns 90 percent of the rows, a broad scan may be cheaper.

Do not guess from the column name. Check the plan against real data.

What to do – Inspect the selected access method and estimated rows instead of deciding that an index works from query time alone.

Creating indexes in MySQL, MariaDB, and PostgreSQL

In MySQL and MariaDB, a single-column index can be created with CREATE INDEX:

CREATE INDEX idx_users_email
ON users (email);

You can express the same change with ALTER TABLE:

ALTER TABLE users
ADD INDEX idx_users_email (email);

Naming is part of administration. A name such as idx_users_email tells me which table and column the index belongs to. Before changing a production table, I check its size, current indexes, write volume, and the possible locking or resource impact. The online behavior of ALTER TABLE depends on the database version, storage engine, and operation.

To inspect existing MySQL indexes, run:

SHOW INDEX FROM users;

The Key_name, Column_name, Non_unique, and Cardinality fields are useful. Cardinality is an estimate based on statistics, not an exact count of distinct values.

PostgreSQL uses similar syntax:

CREATE INDEX idx_users_email
ON users (email);

PostgreSQL’s documentation also describes the cost: indexes require storage and add work to data changes. Finding redundant or unused indexes can be as valuable as adding a new one.

What to do – Record the definition in your migration system and inspect existing indexes before adding another one.

Single-column indexes and composite indexes

A single-column index covers one field. A composite, or multi-column, index stores two or more columns in a defined order:

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, created_at);

This is a sensible candidate for a query like this:

SELECT id, total
FROM orders
WHERE customer_id = 42
  AND created_at >= '2026-01-01'
ORDER BY created_at DESC;

Column order is the detail people most often underestimate. The (customer_id, created_at) order narrows by customer first and then by date. It can also help a query filtering only by customer_id. A query filtering only by created_at may not use the same index efficiently.

This is commonly described as the leftmost-prefix behavior of a B-tree index. For an index on (a, b, c), conditions beginning with a, or with a, b, are generally better matches. Choose the order from actual query patterns, not from the order in which columns happen to appear in the table.

The same columns in a different order are not the same index.

What to do – Order composite-index columns around real query patterns and the leftmost-prefix behavior.

Reading an EXPLAIN plan without guessing

EXPLAIN shows how the database plans to run a query. For MySQL, the basic form is:

EXPLAIN SELECT id, email
FROM users
WHERE email = 'ayse@example.com';

In MySQL output, I first inspect type, possible_keys, key, rows, and Extra. The key column shows the selected index. An empty value is not automatically a problem, but it deserves attention when the query is scanning millions of rows.

• type: Shows the access method; const, eq_ref, and ref are often useful signs for lookup queries.
• possible_keys: Lists indexes that may be usable.
• key: Shows the index selected by the optimizer.
• rows: Estimates how many rows MySQL expects to read.
• Extra: Can show additional work such as Using filesort or Using temporary.

On MySQL versions that support it, including MySQL 8.0.18 and later, EXPLAIN ANALYZE adds actual execution timing and row counts:

EXPLAIN ANALYZE
SELECT id, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Use it carefully in production. The query is executed, so a large or poorly filtered query can add load.

A low rows estimate is not enough by itself. If estimated and actual row counts differ substantially, statistics may be stale or the data distribution may not match the optimizer’s assumptions.

On my Proxmox lab, I once compared an index change using only wall-clock time and nearly kept a misleading result. One run benefited from a warm cache while still reading more rows than expected. Checking the plan and row counts stopped me from carrying that mistake to a customer server.

What to do – Compare the same query before and after the change with EXPLAIN, using comparable data and parameters.

The write cost of an index

Every index has a maintenance cost. When a row is inserted, an indexed value changes, or a row is deleted, the relevant index structure must be updated. Many indexes can mean more disk writes, more memory use, and higher write latency on busy applications.

Indexes also consume storage. Indexing large VARCHAR, TEXT, or binary fields directly can be expensive. MySQL supports prefix indexes for some text columns:

CREATE INDEX idx_articles_title_prefix
ON articles (title(100));

Here, 100 is the indexed character prefix length for this definition. A prefix index is not suitable for every query; it helps only when the prefix distinguishes values well enough. With Unicode data, character and byte limits are not interchangeable, so check the engine’s rules for the column type and character set.

Redundant indexes create another problem. Two indexes with the same columns in the same order are usually unnecessary. An index on (customer_id) may still have a role alongside (customer_id, created_at) in some workloads, but that decision should come from query frequency and plan inspection.

Index definitions can also affect backup size, table maintenance, and replication traffic. On a VPS with limited resources, query speed is only one part of the measurement.

What to do – Check write latency, storage use, and maintenance impact after adding an index, not only the read-query result.

LIKE patterns, functions, and index usage

The way a query is written affects whether an existing index can help. A search beginning with a wildcard usually cannot use a normal B-tree index efficiently:

SELECT id, title
FROM articles
WHERE title LIKE '%linux%';

With %linux%, the beginning of the search string is unknown, so the database cannot jump directly to a useful point in the index tree. By contrast, LIKE 'linux%' is more suitable when the collation and data type are compatible with the index.

Applying a function to the column can cause a similar issue:

SELECT id
FROM users
WHERE LOWER(email) = 'ayse@example.com';

The database is not guaranteed to use a normal email index for this expression. Depending on the engine and version, options include normalizing values in the application, creating a functional index, or using a suitable generated column. The plan decides whether the choice actually helps.

Date and numeric conversions can cause the same kind of trouble. Keeping data types consistent from the start is usually safer than converting the filtered column inside every query. An ORM or WordPress plugin may hide the SQL, so I check the actual statement in the slow query log when a simple-looking request becomes expensive.

If you are troubleshooting How to Fix WordPress Error Establishing a Database Connection, do not stop at the username and password. Slow queries, table structure, and disk latency can also be part of the incident.

What to do – Inspect the generated SQL and avoid wrapping indexed columns in functions unless you have designed an index or generated column for that expression.

A safer way to add an index in production

Adding an index in production is more than running one SQL statement. I start with the slow query, then check how often it runs, how many rows it returns, how large the table is, and how much write traffic the table receives.

1. Choose the slow query from the slow query log or application metrics.
2. Use parameters close to real production values.
3. Save the current access plan with EXPLAIN.
4. Check similar existing indexes with SHOW INDEX or the database catalog views.
5. Create the index in staging first.
6. Compare query time, rows read, and write latency after the change.
7. Keep the rollback command ready if the plan or workload changes unexpectedly.

An index does not repair a badly shaped query. SELECT *, unnecessary JOIN operations, large OFFSET values, and N+1 queries need separate attention. Sometimes the best optimization is asking for less data.

The storage device also affects measurements. SSD latency can change how a query feels, but index design should still come from query plans rather than hardware assumptions. For the storage background, see What Is an SSD? Full Form, Speed and Benefits.

A small WordPress installation can make every query look fast. As traffic, plugins, and content grow, the same query becomes more expensive. After How to Install WordPress on a VPS: Step-by-Step Guide, setting up slow-query monitoring early makes later troubleshooting much less speculative.

I keep index changes in migrations or Ansible rather than relying on a command typed once in a shell. A hand-run statement that nobody records is an easy way to make two otherwise identical servers behave differently. I have cleaned up that kind of drift before; documenting the change takes less time than explaining it during an incident.

What to do – Record the index change as a migration, test it in staging, and compare the production plan and metrics again after deployment.

Before Adding an Index, Check These Items

• Choose the slow query from logs or application metrics.
• Run EXPLAIN with realistic parameters.
• Inspect existing indexes before creating another one.
• Match composite-column order to real query patterns.
• Test the change in staging first.
• Measure rows read, query time, and write latency afterward.
• Keep a documented rollback command ready.

Frequently Asked Questions

Sources

• MySQL 8.0 Reference Manual – Optimization and Indexes — dev.mysql.com
• MySQL 8.0 Reference Manual – EXPLAIN Output — dev.mysql.com
• PostgreSQL Documentation – Indexes — postgresql.org