A lot of the articles on this site over the last months have come out of one project: a web search engine I'm building from scratch. Each of those pieces covers a single incident, like a migration that took six attempts or a graph extension that got built, proven and torn down. What they don't show is the whole arc. This series is that arc, told in order, in five parts. This first part covers how it started and the first big decision: moving the search index out of a relational database.
A skeleton, then a long pause
The repository's first commit is from 25 July 2025. It's a bare Symfony skeleton: 19 files, almost all of them configuration. Over the next five months it got three more commits, the last one on 12 December 2025 with the message "php works". Then nothing for almost six months.
The next commit is a "wip" on 4 June 2026. A week later the project woke up properly: 2 commits on 11 June, 15 on 12 June, 37 on 13 June and 17 on 14 June. By the end of those four days there was a crawler, an indexer and a ranking function, all running against MySQL.
Everything in MySQL
The first version was from scratch in the most literal sense. The inverted index, the structure that maps every word to the pages containing it, lived in two MySQL tables. index_dictionary held each term with its language and a stopword flag. index_entry held one row per term per page, with frequencies and token positions. Ranking was one aggregate SQL query that combined BM25F scoring over title, body and anchor text with a coverage factor, term proximity, a language filter and a PageRank multiplier.
Features arrived fast. On 12 June: BM25 ranking with per-language stopwords computed statistically from the corpus, crawl pacing that honors the Crawl-delay in robots.txt, a boost for title matches, and PageRank as an authority signal. On 13 June: inbound anchor text as its own ranking field, parallel fetching with curl_multi instead of one request at a time, and an indexer that runs as several workers.
Measuring before tuning
The most important commit of that week wasn't a feature. On 13 June I added app:eval-search and a golden set: 13 queries, each paired with the URL patterns a good result should match, scored as mean reciprocal rank (MRR), precision at 1 and recall at 10.
The first numbers were bad. MRR was 0.041, and not one of the 13 queries had the right answer in first place.
The same commit used the harness to make three changes. A coverage factor demotes pages that match only a common subset of the query, such as just "open" in "open data". Proximity re-ranks a candidate pool by how close the query terms sit, using token positions that were stored but never used. Weights were tuned against the numbers: the PageRank weight went from 0.5 to 5, and the BM25 length normalization b from 0.75 to 0.5, because long articles were being over-penalized. MRR went to 0.147.
The commit message already named the next problem: English queries pulling in foreign-language pages. By the time the OpenSearch design was written later that day, the MySQL version scored MRR 0.96 on the same 13 queries, with 12 of them right at position one. That number became the gate for what came next. The golden set has been rebuilt and extended since, including Danish queries such as "aarhus universitet", which should return au.dk. It's still what every ranking change gets measured against.
The first wall
Design document 0001, dated 13 June, starts with numbers: about 90,000 documents, 33 million postings, 1.5 GB of index. Search was fast, but only because MySQL's 4 GB buffer pool held all of it in memory. Postings lists in a B-tree are the wrong data structure for this, and per-query aggregate scans would take seconds or minutes once the index reached hundreds of millions of postings.
The fix was Lucene. Lucene is a Java library and can't be embedded in PHP, so in practice it means a search server spoken to over HTTP. I chose OpenSearch for its Apache 2.0 license. Elasticsearch would have worked the same way, Solr looked heavier to operate from PHP, and the pure-PHP Lucene ports were unmaintained.
System of record, derived index
One rule from that design held for the rest of the project. The relational database is the system of record, and OpenSearch is a derived index that can always be rebuilt from it. The crawl frontier, the link graph, PageRank and page metadata stayed in MySQL. Only the inverted index moved, and authority and aggregated anchor text were pushed into each OpenSearch document as ranking signals.
That rule is what made the move safe. The commit log shows the phases in order, all on 13 June:
- provisioning and per-language index mappings
- dual-writing documents from the indexer, with the MySQL index still running
- an OpenSearch search backend behind a flag, evaluated against the same golden set
- syncing PageRank and anchor text into the documents, and a targeted backfill
- a staged cutover with a readiness indicator
Three days later, on 16 June, a migration dropped index_entry, index_dictionary and term_language. From then on search was OpenSearch only, and the public search path didn't touch the database at all.
The one piece that wasn't from scratch
There's an irony here that took three months to come back around. OpenSearch was the one component of this from-scratch search engine that I didn't write, and it would also turn out to be the most expensive one to run. That's where part 5 ends up.
Before that, the hard problem moved from finding pages in the index to finding pages at all. In part 2 the corpus grows about forty-fold in three weeks, outgrows one server, then two, and the database underneath it gets replaced.
Leave a comment
Comments are reviewed before they appear.