DATA & INDEXING
Database index vs search index
"We already have indexes" is the first thing every engineer says. Both are called indexes. They answer different questions.
What a database index does
A database index is a B-tree: a sorted structure over one column, or over a leading prefix of several, that lets the engine find a value in a few hops instead of reading the table. Equality and range are its two moves. WHERE customer_id = 1042 descends to one leaf. WHERE order_date BETWEEN … descends to the first leaf in the range and reads along. Primary keys, foreign keys and the columns a screen filters on alone are exactly what it was designed for, and for those it is unbeatable.
Where it stops
LIKE '%jacket%' cannot use it. A B-tree is sorted by the start of the value, so a pattern that begins with a wildcard has nowhere to descend to and the engine scans every row. Combinations of fields need a composite index per combination, in the right column order, or the planner falls back to scanning. Counting per value still scans: an index can find the matching rows, but GROUP BY brand over them is a pass over the rows, repeated for every facet the screen shows. And there is no such thing as relevance. A row matches a predicate or it does not; nothing in the structure can say that one match is better than another.
What a search index does
A search index starts by tokenizing text: a product title is split into words, lowercased, reduced to a stem, so Jackets and jacket become the same term. Each text field gets an inverted index, a map from term to the list of records that contain it, which is why finding every record that mentions jacket is one lookup - the list for jacket reads 7, 41, 302 and so on - rather than a scan. Each field also gets doc values, its values stored together in record order, which is what makes sorting and counting cheap after the match. Every result carries a relevance score built from how rare a term is, how often it appears, and which field it appeared in. And typo tolerance comes from edit distance: jaket is one edit from jacket, so it matches.
None of that replaces the B-tree. The search index does not know what a foreign key is and does not want to. It answers a different question.
The two also live in different places. A database index sits next to its table and is updated inside the same transaction as the row, which is what keeps it exact and what makes it cost a write. A search index is a separate copy, refreshed from the database on a schedule, so it costs the database nothing per write and is as fresh as its last import. That trade - exactness on the write side, speed on the read side - is the same one that runs through What is an index server?, and it is why the two structures end up in two systems rather than one.
Why "just add an index" plateaus
Three reasons, in the order teams meet them.
Write amplification. Every index has to be updated on every insert and update. The first reporting index costs little. By the fifth, a meaningful share of each transaction is spent maintaining structures that exist for screens the transaction never touches.
Combinatorics. A screen that lets people filter by any of six fields - name, brand, category, size, price, status - can ask for 63 combinations of them. Six single-field indexes cover six. Covering the rest means a composite per combination, and because a composite only helps queries that use its leading columns in order, the real number is larger still.
- name
- brand
- category
- size
- price
- status
63 combinations · six single-field indexes cover six of them · the rest need a composite each, and column order multiplies that
The planner gives up. Faced with a six-field predicate and a handful of partial indexes, the optimizer estimates, picks one, and scans for the rest. The query that was fast at 200,000 rows is a scan at five million, and the next index moves the plateau by a few percent.
Side by side
| Criterion | Database index | Search index |
|---|---|---|
| Question answered | Find the rows where this column equals, or falls within, a value | Find, rank and count the records that match words, fields and filters |
| Structure | B-tree per column or composite | Inverted index per text field, exact-value structures per field, doc values for sorting and counting |
| Text matching | Prefix onlyLIKE 'abc%' can use it; '%abc%' cannot | WordsTokenized, stemmed, within an edit distance |
| Multi-field search | One composite per combination | Any combinationEach field indexed once |
| Counts per value | Still a scanGROUP BY over the matching rows | FacetsColumn pass over doc values |
| Ranking | Not a conceptA row matches or it does not | Relevance score per result |
| Effect on writes | Every index slows every write | None on the sourceThe copy is refreshed on a schedule |
| Lives where | Inside the database, next to the table | In the index server, as a separate copy |
When each is right
Both, usually. Database indexes for keys, foreign keys and the single-column filters that transactional screens use: they belong in the database and nothing here suggests removing them. A search index for the screens people search, filter and count on: the catalog, the listing with the filter sidebar, the console that searches six fields. Those move to a copy built for them, and the reporting indexes that were added for them can come off the database, which makes the writes faster too.
The engineer who says "we already have indexes" is right about the first kind. This article is about the second. RDBMS vs index server takes the comparison from the index up to the whole system.