Why Database Indexes Speed Up Some Queries and Not Others

Database indexes speed up queries that filter, join, or sort on the indexed columns, but they do nothing for queries that don’t use those columns or that scan a large portion of the table. An index is a separate, sorted structure that lets the database find matching rows without reading every row, so its benefit depends entirely on how the query accesses the data.

Formula

The calculator above computes your average pace and speed for a run, ride, or walk. Enter the total time in minutes and the distance in kilometers. The formulas are:

• Pace = Time (min) ÷ Distance (km) — the result is minutes per kilometer.
• Speed = Distance (km) ÷ (Time (min) ÷ 60) — the result is kilometers per hour.

Variables: t = total time in minutes, d = distance in kilometers. Pace is expressed as min/km; speed as km/h.

Worked Example

Using the default values: t = 50 minutes, d = 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 per kilometer and an average speed of 12.00 km/h.

Why Indexes Help Certain Queries

Indexes shine when a query needs to find a small subset of rows. For example, a query like SELECT * FROM orders WHERE customer_id = 123 can use an index on customer_id to jump directly to the matching rows. Without an index, the database performs a full table scan — reading every row in the table to check the condition. On a table with millions of rows, that scan can take seconds or minutes, while the index lookup might take milliseconds.

The same logic applies to joins. If you join two tables on a column that is indexed in both, the database can quickly match rows. Sorting (ORDER BY) and grouping (GROUP BY) also benefit because the index already stores values in sorted order, so the database can avoid a costly sort step.

Why Indexes Don’t Help Other Queries

An index is useless if the query doesn’t reference the indexed column. For instance, an index on last_name does nothing for a query filtering on city. Even if the column is indexed, the database may ignore the index if the query retrieves a large fraction of the table. Reading an index plus the table rows is often slower than just scanning the table when you need, say, 30% of the rows. This is why the query optimizer chooses a full scan for such cases.

Another common reason an index is skipped: using a function on the column. WHERE UPPER(last_name) = 'SMITH' won’t use a normal index on last_name because the index stores the original values, not the uppercased versions. Similarly, leading wildcard patterns like LIKE '%smith' can’t use a standard B-tree index.

How Indexes Work Internally

Most database indexes use a B-tree (balanced tree) structure. The index stores the indexed column’s values in sorted order, along with pointers to the actual rows. When you search for a value, the database traverses the tree from root to leaf, which takes O(log n) steps. For a table with a million rows, that’s about 20 comparisons — far faster than scanning a million rows.

Indexes also come in other types. A composite index covers multiple columns, e.g., (country, city). It helps queries that filter on both columns or just the first column, but not on the second column alone. A covering index includes all columns needed by the query, so the database can answer the query entirely from the index without touching the table — a big performance win.

When to Create an Index

Indexes are most valuable for columns used in WHERE clauses, join conditions, and ORDER BY on large tables. A good rule of thumb: create indexes for columns that appear frequently in filters and have high selectivity (many distinct values). For example, an index on order_id is highly selective; an index on status with only three possible values is not — it might return 30% of the table, so the optimizer may ignore it.

Start with the most important queries. Use the database’s query plan (EXPLAIN in PostgreSQL/MySQL, or the execution plan in SQL Server) to see whether an index is being used. If a query does a full scan on a large table and runs often, adding an index on the filtered column is usually the fix.

Costs and Trade-offs

Indexes are not free. Every index consumes disk space and must be updated on every INSERT, UPDATE, or DELETE. If a table has many indexes, write operations slow down because the database has to maintain each one. A table with 10 indexes could see noticeably slower inserts than one with none. That’s why you shouldn’t index every column — only those that matter for read performance.

Also, indexes can become fragmented over time, especially with frequent updates. Periodic maintenance (like REINDEX or rebuilding) can keep them efficient, but it’s a background cost.

Common Mistakes and Misconceptions

• Indexing too many columns — Every index adds write overhead and storage. Only index what your queries actually use.
• Expecting indexes to help with small tables — On a table of a few hundred rows, a full scan is so fast that an index adds no benefit and may even hurt due to extra lookups.
• Ignoring composite index column order — For an index on (a, b), queries filtering on b alone won’t use it efficiently.
• Using functions on indexed columns — WHERE DATE(created_at) = '2024-01-01' defeats a normal index on created_at. Use a range condition instead.
• Thinking indexes always speed up queries — If the query returns a large portion of the table, a scan is often faster than an index lookup.

Alternatives to Indexes

When an index isn’t the right tool, other options exist. Partitioning splits a large table into smaller pieces based on a key (like date), so queries that filter on that key only scan the relevant partition. Materialized views precompute and store query results, which can speed up complex aggregations. Denormalization (adding redundant columns) can reduce the need for joins. For full-text search, specialized indexes like inverted indexes are better than B-trees. Each approach has its own trade-offs, but they’re worth considering when indexes alone don’t solve the performance problem.

Frequently Asked Questions

Why does my index not get used?

Common reasons: the query returns too many rows (the optimizer prefers a scan), the column is wrapped in a function, the index doesn’t match the query’s column order, or the table is so small that a scan is faster. Check the query plan to see the optimizer’s reasoning.

Should I index every column I filter on?

No. Indexes cost storage and slow down writes. Only index columns that appear in frequent, performance-critical queries. For columns with few distinct values (like a boolean), an index rarely helps.

What’s the difference between a clustered and non-clustered index?

A clustered index defines the physical order of rows in the table, so a table can have only one. A non-clustered index is a separate structure that points to the rows. Clustered indexes are often on the primary key, but you can choose a different column if it matches your most common lookups.

Can an index speed up a query that uses OR?

Sometimes. If the OR conditions are on indexed columns, the database may use a bitmap or index merge. But if one side of the OR is not indexed, the database might fall back to a full scan. Rewriting the query as a UNION of two indexed queries can help.

How many indexes is too many?

There’s no fixed number. The right amount depends on your workload. A rule of thumb: keep the total number of indexes low (single digits per table) unless you have a read-heavy system that can tolerate slower writes. Monitor write latency and index usage to decide.

Related

• Network Switch Internet Speed: How to Test the Connection
• Find Venmo Pending Split & Scheduled Payments
• Arc Raiders Grapple vs Snap Hook & Zipline

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