Database Indexes: How They Find Matching Rows Faster

Database indexes find matching rows faster by using a sorted or hashed data structure that lets the database jump directly to the relevant rows instead of scanning every row in the table. You enter the indexed column value in a query, and the index tells the database exactly where those rows are stored, reducing the search from a full table scan to a few quick lookups. This article explains how indexes work, why they speed up queries, and how to use them effectively.

Formula

For this calculator, the formulas are:

• Pace (min/km) = Finish time (minutes) ÷ Distance (km)
• Speed (km/h) = Distance (km) ÷ (Finish time (minutes) ÷ 60)

Where:

• Finish time (t) is the total time in minutes.
• Distance (d) is the distance in kilometers.
• Pace is the time per kilometer, in minutes per kilometer.
• Speed is the average speed, in kilometers per hour.

Worked Example

Using the default values: finish time = 50 minutes, distance = 10 km.

1. Pace = 50 ÷ 10 = 5.00 min/km
2. Speed = 10 ÷ (50 ÷ 60) = 10 ÷ 0.8333 = 12.00 km/h

The calculator shows an average pace of 5.00 min/km and an average speed of 12.00 km/h.

Why Indexes Matter

Without an index, a database performs a full table scan: it reads every row from the first to the last, checking each one against the WHERE clause. For a table with millions of rows, that means millions of disk reads, which can take seconds or even minutes. An index is a separate data structure—usually a B-tree or a hash table—that stores a copy of the indexed column(s) in a sorted order, along with pointers to the actual rows. When a query filters on that column, the database can traverse the index in logarithmic time (O(log n)) or constant time (O(1) for hash indexes), finding the relevant row pointers without touching most of the table.

How B-Tree Indexes Work

The most common index type is the B-tree. It is a balanced tree where each node can have multiple children, and the leaves contain the indexed values and pointers to the rows. Searching a B-tree is like looking up a word in a dictionary: you start at the root, compare the target value with the keys in the node, and follow the appropriate child pointer. Because the tree is balanced, every search takes roughly the same number of steps, proportional to the logarithm of the number of entries. For example, a B-tree with 1,000,000 entries might only need about 20 comparisons to find a match. After finding the first match, the database can also scan the leaf nodes sequentially to retrieve all rows with the same value, which is why B-trees are efficient for range queries like WHERE age BETWEEN 20 AND 30.

Hash Indexes and When They Are Used

Hash indexes are another common type. They apply a hash function to the indexed column value and store the hash in a hash table, with a pointer to the row. Lookups are extremely fast—typically O(1) time—but hash indexes only support equality comparisons (= or IN), not range queries. They are also not sorted, so they cannot help with ORDER BY or range conditions. Many databases use hash indexes for in-memory tables or as a secondary index when equality is the only query pattern. For example, a hash index on a user ID would make WHERE user_id = 12345 nearly instantaneous, but it would not help with WHERE user_id > 1000.

Composite Indexes and Leftmost Prefix Rule

A composite index is an index on multiple columns, like INDEX (last_name, first_name). The index sorts by the first column, then by the second within the first. This allows efficient queries that filter on the first column alone, or on both columns. However, the leftmost prefix rule applies: the index can only be used when the query filters on the leftmost column(s) in order. A query that filters only on first_name cannot use the composite index because the index is sorted by last_name first. To get the most out of a composite index, design it with the most selective column first and match the query’s WHERE clause order.

Indexing Costs and Trade-offs

Indexes are not free. Every index adds overhead to write operations (INSERT, UPDATE, DELETE) because the database must update the index whenever the indexed column changes. This can slow down write-heavy applications. Indexes also consume disk space and memory, and a poorly chosen index can be worse than no index at all. For example, indexing a column with very few distinct values (like a boolean) may not speed up queries much, because the index still returns a large fraction of the table. Similarly, indexing every column in a table can bloat the database and degrade performance. The key is to index columns that are frequently used in WHERE, JOIN, and ORDER BY clauses, and to avoid over-indexing.

When Indexes Do Not Help

Indexes are not a cure-all. They do not help with queries that use functions or expressions on the column, such as WHERE UPPER(name) = 'ALICE', unless the index is a function-based index. They also do not help with leading wildcard searches like WHERE name LIKE '%smith', because the database cannot use the sorted order to find matches. In those cases, the database may fall back to a full table scan. Additionally, for very small tables, a full scan is often faster than using an index because the overhead of reading the index and then the table is higher than just reading the table directly. Finally, indexes do not help with queries that select a large percentage of the table (e.g., WHERE status = 'active' when 90% of rows are active); the database may ignore the index and scan the table instead.

Frequently Asked Questions

What is the difference between a clustered and non-clustered index?

A clustered index determines the physical order of rows in the table. There can be only one clustered index per table, and it is often the primary key. A non-clustered index is a separate structure that stores the indexed columns and a pointer to the row (or to the clustered index key). Clustered indexes are faster for range queries because rows are stored together, but they can be slower for inserts that cause page splits.

How do I know which columns to index?

Look at your query patterns: columns that appear in WHERE clauses, JOIN conditions, and ORDER BY are good candidates. Also consider the selectivity—columns with many distinct values (like email) benefit more than columns with few distinct values (like gender). Use the database’s query plan (EXPLAIN) to see which indexes are actually used and where full scans occur.

Can an index slow down my database?

Yes, because every write operation must update all indexes on the table. If you have many indexes, inserts and updates become slower. Also, indexes consume disk space and memory. The trade-off is usually worth it for read-heavy applications, but for write-heavy ones, keep the number of indexes minimal.

What is a covering index?

A covering index is an index that contains all the columns needed for a query, so the database can answer the query entirely from the index without accessing the table rows. This is very fast because the index is smaller than the table and often cached in memory. For example, an index on (last_name, first_name) covers a query that selects only those two columns.

Why does my index not get used?

There are several reasons: the query uses a function on the indexed column, the index is not selective enough, the table is small, or the optimizer estimates that a full scan is cheaper. Also, if the query uses a leading wildcard in a LIKE pattern, the index cannot be used. Check the EXPLAIN plan to see why the index was ignored.

Do indexes help with JOINs?

Yes, indexes on the join columns can dramatically speed up joins. For example, if you join on orders.customer_id = customers.id, an index on orders.customer_id and on customers.id (usually the primary key) allows the database to look up matching rows quickly instead of scanning the entire orders table for each customer.

Related

• Free VPN in Opera Browser for Android: How It Works
• VideoProc vs GoPro & DJI: Is Video Processing Easy?
• Fix Halloween Animatronic Not Moving, Lighting, or Sounding

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2