Normalized MySQL is great for writing data correctly. It’s not great for serving it fast. Once a project entity spans tasks, members, documents, orders, and comments, every “project overview” request turns into a wall of joins and aggregations — repeated identically for thousands of requests a day.
The fix isn’t picking a faster database. It’s moving the expensive work from request time to change time.
The problem with query-time joins
A single overview needs data from 6–8 normalized tables: joins, counts, sums, grouping, then application-level mapping. None of that is wrong — it’s just wasteful when the same computation repeats for every read while the underlying data barely changes.
Separate the write model from the read model
MySQL stays the source of truth for commands (CreateProject, AddTask, AssignMember). Its 3NF schema is correct for referential integrity and transactional writes. The read side gets its own denormalized projection — a precomputed document shaped exactly like what the API needs to return.
{
"id": "project-123",
"name": "Office",
"status": "ACTIVE",
"metrics": { "taskCount": 84, "completedTaskCount": 52, "memberCount": 12 },
"financials": { "budget": 250000, "spent": 172000 },
"updatedAt": "2026-08-08T00:00:00Z"
}
Keeping it in sync: CDC, not dual writes
Application-level dual writes (write MySQL, then write Mongo/Meilisearch) create a split-brain risk if the second write fails. Debezium reads MySQL’s binlog and emits change events to Kafka instead — MySQL writes only to itself, and everything downstream reacts asynchronously.
The projection service is the real component
Mongo vs. Meilisearch is a secondary decision. The component that actually matters is the projection service: given “task 456 changed,” it has to resolve which project owns it, recompute the affected aggregates, and emit a clean business-level event — not a raw table-shaped CDC blob. This can be a Go service, Kafka Streams, or Flink; the logic is the same regardless of runtime.
Publishing an intermediate project.projection.v1 topic (rather than writing Debezium's raw events straight into the datastore) decouples the read stores from MySQL's schema entirely — they only ever see the business document.
Choosing the destination store
Don’t force one system to do both jobs well. A common split: a reporting-oriented store (Mongo, Postgres, ClickHouse) for dashboards and aggregation, Meilisearch dedicated purely to interactive search — both fed from the same projection stream.
How retrieval actually works, engine by engine
The table above compares capabilities. It’s worth going one level deeper into how each engine actually resolves a read, because that’s where the real cost difference lives.
3NF: retrieval is query-time relational algebra
A project overview in MySQL is a logical plan the optimizer builds fresh (or from a cached plan) on every execution:
SELECT p.id, p.name, p.status,
COUNT(DISTINCT t.id) AS task_count,
COUNT(DISTINCT CASE WHEN t.status='DONE' THEN t.id END) AS completed_count,
COUNT(DISTINCT m.id) AS member_count,
SUM(o.amount) AS spent
FROM projects p
LEFT JOIN projecttasks t ON t.projectid = p.id
LEFT JOIN projectmembers m ON m.projectid = p.id
LEFT JOIN projectorders o ON o.projectid = p.id
WHERE p.id = ?
GROUP BY p.id; Under InnoDB, each LEFT JOIN is a nested-loop or block-nested-loop lookup against a secondary index (project_id), followed by a trip back to the clustered index (the primary-key B-tree) for any column not covered by the index — this is the "bookmark lookup" cost that makes wide SELECTs on indexed foreign keys still expensive. The COUNT/SUM aggregates force a temporary table or filesort when the optimizer can't stream-aggregate. None of this is a bug — it's exactly what a normalized schema is supposed to do — but it means cost scales with the number of child rows per project, and it's recomputed identically for every request, warm cache or not.
The read model: retrieval as a single keyed lookup
Once the projection exists, retrieval degenerates to:
SELECT document FROM projectreadmodels WHERE projectid = ?; one clustered-index point lookup, zero joins, zero aggregation. Same idea in Mongo or Meilisearch below — the point is the shape_ of the read changed, not just the engine.
MongoDB retrieval internals
A findOne({_id: "project-123"}) resolves through the storage engine (WiredTiger) as a single B-tree lookup on the _id index, which is clustered by default — the document bytes live at the leaf, so there's no second hop to fetch the row (unlike InnoDB's secondary-index bookmark lookup above). The BSON document is deserialized once and returned as-is; there's no join and no runtime schema reconciliation because the shape was fixed at write time.
For filtered queries
db.projects.find({ status: "ACTIVE", "location.city": "New York" }) — Mongo needs a compound index ({status: 1, "location.city": 1}) or it falls back to a collection scan. The query planner picks a winning index by running candidate plans and caching the plan with the lowest number of documents examined; explain("executionStats") shows exactly which index was chosen and how many docs were scanned vs. returned — worth checking in production, since an unindexed nested-field filter silently degrades to COLLSCAN.
Aggregation ($match → $group → $sum) runs as a pipeline of streaming stages inside the same storage engine — no separate OLAP layer — which is why Mongo can serve both point retrieval and reporting rollups from the same denormalized collection, at the cost of the aggregation still touching every matching document at query time (it isn't materialized unless you use $merge/$out to persist it).
Meilisearch retrieval internals
Meilisearch doesn’t do B-tree lookups for search queries — it resolves against a prebuilt inverted index: for every indexed field, each word maps to the list of document IDs (and positions) containing it. A search for ofice renovtion becomes, roughly:
- Tokenize the query into
ofice,renovtion. - Typo-tolerant matching — for each token, Meilisearch’s Levenshtein-automaton-based matcher finds index terms within edit distance 1 (short words) or 2 (longer words), so
oficematchesofficeandrenovtionmatchesrenovationwithout an exact-string index hit. - Set intersection — the matched terms’ posting lists are intersected/unioned per Meilisearch’s query rules to get a candidate document set.
- Ranking — candidates are sorted by a configurable, ordered rule chain (
words→typo→proximity→attribute→sort→exactnessby default), each rule acting as a tiebreaker for the previous one, not a weighted score sum. This is a meaningfully different retrieval model from Mongo's index-scan-and-filter: it's built to answer "what best matches this fuzzy text," not "what documents satisfy this predicate." - Filters (
status = ACTIVE) and facets are applied via separate filterable-attribute indexes intersected with the text-match candidate set, not a table scan.
Because ranking and typo-tolerance are baked into index structure and query execution rather than bolted on with LIKE '%...%' or regex, latency stays flat as the corpus grows — the cost is dominated by posting-list intersection size, not document count scanned.
Why this matters for the projection design
The retrieval mechanics dictate what the projection service should emit for each store: MongoDB needs compound indexes matching your actual filter/sort patterns (unindexed nested-field queries silently full-scan), while Meilisearch needs filterableAttributes and sortableAttributes declared explicitly at index-creation time — fields not declared there aren't usable for filtering at query time no matter how they're structured in the document. Getting this configuration right at projection-design time is what keeps "cheap read" cheap in practice, not just in theory.
Don’t cram everything into one document
As projects grow to include thousands of tasks, comments, and documents, resist putting it all into one giant project record. Keep a lightweight project_overviews index separate from a fine-grained entity index (task-456, comment-789, each carrying projectId/projectName). This keeps the overview document small and lets search return matches across tasks, comments, and documents without bloating the root object — it also sets up cleanly for hybrid keyword + vector retrieval later.
Two things to build in from day one
- Idempotent upserts. CDC events can and will be replayed (rebalances, restarts, reindexing). Projection writers must be
UPSERT project-123, never blind inserts. - Rebuildability. The read store is never the source of truth. If projection logic has a bug, you should be able to replay Kafka or re-derive from MySQL and regenerate the entire read model from scratch.
The actual lesson
The performance win isn’t “MongoDB is faster than MySQL” or “Meilisearch beats a SQL query.” It’s that the join-and-aggregate work moved from every request to every write. Reads become a single cheap lookup because the expensive computation already happened once, at write time, instead of being repeated on demand.
MySQL stays authoritative for writes. Everything downstream is disposable, replayable, and shaped for exactly the workload it serves.