Database Indexes and Query Speed

The book index comparison

Suppose you want every mention of “backup” in a thick reference book. You could read every page from the start, or you could turn to the index at the back, find “backup” in alphabetical order, and jump straight to the listed pages. The index does not contain the content. It is a separate, sorted list that points to where the content is.

A database index works the same way. It is an extra structure, kept alongside a table, that lets the database find rows matching a value without reading the whole table.

What happens without an index

When you run a query such as:

SELECT * FROM customers WHERE email = 'ana@example.com';

and no index exists on email, the database has to check the email of each row in turn. This is often called a full table scan. With a few hundred rows, you will not notice. With millions of rows, the same query can take noticeably longer, and if many users run such queries at once, the whole system can slow down.

How an index helps

Most indexes are stored as a tree structure, commonly a variation called a B-tree. The details are not essential, but the idea is. Values are kept in sorted order, and the structure lets the database narrow down the location in a few steps instead of checking every entry. Each step rules out a large part of the data, in the same way that guessing a number between 1 and 1,000 takes only about ten guesses if you are told “higher” or “lower” each time.

Once the index has located the matching entry, it points to the actual row, which the database then fetches.

What indexes cost

An index is not free, which is why databases do not index everything automatically.

• Storage. The index is an extra copy of some data, plus pointers, so it takes space. A table with many indexes can be larger than the data itself.
• Slower writes. Every time a row is inserted, changed or deleted, the database must update the indexes too. A table with a lot of writing and many indexes pays for each of them.
• Maintenance. Indexes can become fragmented or out of date over time, and some systems need occasional upkeep.
• Planning complexity. More indexes mean more choices for the database when it decides how to run a query, and sometimes it chooses poorly.

The trade-off is faster reading in exchange for slower writing and more storage. Whether that is worthwhile depends on how the table is used.

Which columns to consider

Good candidates are columns that appear often in:

• WHERE filters, such as an email address, order number or status you search by.
• Join conditions. Foreign key columns used to match tables, as in SQL joins, are frequent candidates. Some systems index the primary key automatically but not foreign keys.
• ORDER BY clauses, since an index that is already sorted can save the database from sorting at query time.

Poor candidates include:

• Columns that are rarely used for searching.
• Columns with very few distinct values, such as a yes/no flag, where an index may not narrow the results much. This depends on the system and the data.
• Tables that are tiny, where scanning is already fast.
• Tables that are written to constantly and read rarely.

Primary keys, unique indexes and composite indexes

A primary key is normally backed by an index so that lookups by key are quick and duplicates can be refused. A unique index enforces that no two rows share a value, which is useful for things like email addresses where duplicates would be an error.

A composite index covers more than one column, for example last name and then first name. Order matters. Such an index generally helps queries that filter by the first column, or by both, but may do little for queries that filter only by the second. Think of a phone book sorted by surname: it is no help if you only know a first name.

Writing queries that can use an index

Having an index does not guarantee it will be used. Some habits stop the database from using one:

• Wrapping the indexed column in a function, such as converting it to lowercase before comparing, can prevent a plain index from being used, though some systems allow indexes on expressions.
• Searching for text that ends with a pattern, such as LIKE '%smith', cannot use an ordinary index well, while LIKE 'smith%' often can.
• Comparing values of different types can force conversions.
• Selecting more columns than needed can cause extra row lookups. Choosing only the columns you need, as discussed in SQL queries, helps.

Finding slow queries

The best approach is to measure rather than guess. Most database products can show a query plan (sometimes called an execution plan or explain output), which describes how the database intends to run a statement, including whether it will scan the whole table or use an index. Comparing the plan before and after adding an index shows whether it helped. Slow-query logs and system monitoring can reveal which statements deserve attention first, and reading system logs is a good habit for spotting patterns.

A sensible process

1. Find a query that is actually slow, using real timings.
2. Look at its plan and identify the filter, join or sort involved.
3. Add an index on a test copy first and check the plan and timing.
4. Check that writes to the table are not noticeably hurt.
5. Roll it into production through the normal approval steps, as in other change management.
6. Review occasionally and remove indexes that nothing uses.

Indexes in other settings

Search engines and NoSQL databases have their own indexing ideas, and the question of how queries are served is part of what separates relational and NoSQL designs. Large analytical systems, such as data warehouses, often rely on different techniques, such as storing data by column and splitting it into partitions, rather than many traditional indexes.

Indexes are one of the most effective tools for speeding up reads, but they are a trade, not a free upgrade. Add them for a reason, measure the effect, and remove the ones that no longer earn their keep.