Chapter 21 · Specialized MySQL Capabilities: NDB, Document Store, Spatial, and Search
Choosing Native Features vs Dedicated Systems for Search, Analytics, and Distributed Scale
Turn specialized MySQL features into architecture decisions by comparing consistency, query shape, scale, latency, operational burden, and duplication rather than choosing from a feature checklist.
Learning outcomes
ServiceHub now has several native options: relational SQL, JSON and Document Store, spatial types, FULLTEXT, partitioning, replicas, Group Replication, and—if justified—a separate NDB topology. A feature list can make MySQL appear capable of replacing a document database, search engine, GIS service, analytics warehouse, and distributed write store simultaneously. That is the wrong architecture method. This final lesson chooses boundaries from workload semantics, consistency, scale, latency, failure modes, ownership, and operational cost.
Build a decision matrix that compares requirements rather than product names.
Distinguish scale-up, read replicas, partitioning, NDB distribution, and external-system scale patterns.
Account for dual-write/change-data-capture lag, data ownership, reconciliation, privacy, and recovery when duplicating data.
Use native MySQL features when they keep the system simpler without violating the workload contract.
Write architecture decisions with measurable exit criteria instead of permanent technology allegiance.
Start with workload questions, not technologies
| Question | Evidence to collect |
|---|---|
| What consistency is required? | read-after-write, transaction boundary, stale-read tolerance, reconciliation rules |
| What are the dominant query shapes? | point lookup, join, range, text ranking, spatial region, aggregate scan |
| Where is scale pressure? | writes, reads, storage, search corpus, analytical CPU, network |
| What latency matters? | p50/p95/p99 by operation, not one average |
| How fresh must derived data be? | seconds/minutes/hours and behavior during pipeline outage |
| Who operates each system? | on-call ownership, backup/recovery, upgrades, security, observability |
| What is the failure contract? | degrade, reject, queue, serve stale, or fail over |
Only after these questions are answered should architecture compare MySQL-native and specialized systems. The cheapest system to operate is usually the one you do not add—but avoiding a required specialized capability can be more expensive than adding it.
Native capability versus specialized boundary
| ServiceHub need | Reasonable MySQL-first option | Escalate when... |
|---|---|---|
| stable OLTP relationships | InnoDB relational schema + indexes | write partitioning or global distribution becomes the proven bottleneck |
| heterogeneous asset metadata | JSON column or Document Store/X DevAPI | document autonomy/query scale/operational ownership justify a separate store |
| technician/service geography | spatial types/functions/indexes | routing, rich GIS analysis, map processing, or geospatial scale exceeds MySQL fit |
| knowledge article search | InnoDB FULLTEXT | typo tolerance, analyzers, semantic ranking, independent search scale become required |
| reporting/analytics | indexes, replicas, summaries | large scans/columnar workloads harm OLTP or need independent compute/storage |
| horizontal write scale + rapid HA | evaluate NDB 8.4 when workload fits | NDB constraints/operations do not fit; application sharding/other distributed DB may be better |
| data lifecycle | partitioning/archiving | partitioning is confused with cross-node distribution or cannot meet retention economics |
Partitioning, replicas, and NDB solve different scale problems
Table partitioning divides one logical MySQL table into partitions inside the server/storage architecture; it is not automatic cross-server sharding. Read replicas provide additional read capacity but introduce staleness and do not remove the primary write bottleneck. Group Replication/InnoDB Cluster provides HA and routing around an InnoDB topology; its usual single-primary mode is not a horizontal write-sharding system. NDB is a different distributed storage engine/topology designed to partition data across data nodes and can scale writes for fitting workloads.
Need: more reporting capacity -> replica / analytics boundary may helpNeed: retire old time ranges quickly -> partitioning / archive lifecycle may helpNeed: survive server loss safely -> InnoDB Cluster / Group Replication may helpNeed: distribute fitting OLTP writes across storage nodes -> evaluate NDB or another distributed data architectureNeed: fuzzy multilingual semantic search -> FULLTEXT alone is probably the wrong boundaryThe mechanism must match the bottleneck.Data duplication creates a consistency system you must own
Adding Elasticsearch/OpenSearch, a document store, a data warehouse, or any secondary system usually duplicates some MySQL data. That creates a pipeline and a new failure mode. A transaction may commit in MySQL before the search index updates; a consumer may retry; an event may arrive twice; a reindex may lag for hours. The architecture must define the system of record, propagation mechanism, idempotency, freshness SLO, reconciliation, deletion/privacy propagation, and rebuild procedure.
| Concern | Required decision |
|---|---|
| source of truth | which system wins after disagreement? |
| propagation | transactional outbox/CDC/batch/dual write and why |
| ordering/idempotency | how duplicates and reorderings are handled |
| freshness | maximum tolerated lag and user-visible behavior |
| reconciliation | how drift is detected and repaired |
| delete/privacy | how erasure/retention reaches every copy |
| recovery | can the derived system be rebuilt from authoritative data? |
A dedicated system is not wrong because it duplicates data. It is wrong if the team has no explicit model for the duplicated data lifecycle.
Failure case: choose a specialized feature from a feature checklist
Imagine ServiceHub selects NDB because the requirement says “must scale,” Document Store because “schema must be flexible,” and an external search engine because “users need search.” None of those phrases identifies a measurable workload. The repaired design asks: writes per second at what latency? Which fields actually vary? What search errors do users make? How stale may search results be? Which system is authoritative?
USE servicehub_special_lab;INSERT INTO architecture_decisions(capability,workload_need,chosen_boundary,evidence)VALUES('asset metadata', 'device-family-specific optional attributes with relational identity and work-order joins', 'MySQL JSON / X DevAPI collection where useful; keep stable invariants relational', 'flexibility needed, but no independent document-scale requirement proven'),('service geography', 'site distance and bounded region lookups', 'MySQL spatial types/functions; evaluate GIS service only for routing/rich geospatial analysis', 'current query shape is small OLTP spatial lookup'),('knowledge search', 'technician keyword search with natural and Boolean terms', 'InnoDB FULLTEXT initially', 'native token search fits; exit if typo/analyzer/semantic/ranking requirements fail evaluation'),('analytics', 'operational dashboards plus future long-running historical analysis', 'keep OLTP summaries/replica now; add OLAP system when scans or compute isolation require it', 'no evidence yet that analytical workload requires an independent engine');SELECT decision_id,capability,chosen_boundary,evidenceFROM architecture_decisionsORDER BY decision_id;These records are intentionally revisable. Architecture becomes stronger when each decision contains an exit criterion: the observable condition that means the current boundary no longer meets the contract.
Build an acceptance matrix before adding a second system
Capability: __________________________System of record: ____________________Primary query shape: __________________Consistency / staleness contract: ______Peak + p95/p99 target: _________________Data volume / growth: __________________Failure behavior: ______________________Backup / rebuild path: _________________Security / privacy owner: ______________On-call owner: _________________________MySQL-native candidate: ________________Specialized candidate: _________________Representative test result: ____________Operational-cost difference: ___________Decision: ______________________________Exit criterion / revisit trigger: _______Do not fill this record with vendor marketing numbers. Use your representative workload, your network, your schema, your failure drills, and your team’s operational capacity.
Final Chapter 21 verification and cleanup
USE servicehub_special_lab;SELECT COUNT(*) AS architecture_decisionsFROM architecture_decisions;SELECT COUNT(*) AS geo_sitesFROM service_sites_geo;SELECT COUNT(*) AS yard_objectsFROM yard_objects;SELECT COUNT(*) AS knowledge_articlesFROM knowledge_articles;SELECT capability,chosen_boundaryFROM architecture_decisionsORDER BY decision_id;-- Document collection count is verified through MySQL Shell X DevAPI.-- After exporting your decision evidence, optional cleanup:-- DROP DATABASE servicehub_special_lab;-- DROP USER IF EXISTS 'special_doc'@'127.0.0.1';Chapter 22 is the production capstone. It will force these boundary decisions into one architecture with service-level objectives (SLOs), workload and capacity assumptions, schema/index/security design, load tests, backups/PITR, replication/HA, monitoring, failure drills, and a defended final design.
Knowledge check
- Why is “MySQL supports feature X” not enough to choose it?
- Does table partitioning horizontally scale writes across MySQL servers?
- What new problem appears when data is copied to a search/OLAP/document system?
- What is an exit criterion?
- What is the preferred default when native MySQL meets the requirement cleanly?
Reveal answers
- Architecture depends on workload, consistency, scale, latency, failure, and operational requirements—not syntax availability.
- No. It organizes a table into partitions; it is not automatic cross-server sharding.
- You must own propagation, lag, duplicates/order, reconciliation, privacy/deletion, and rebuild/recovery.
- A measurable condition that triggers reconsideration because the current architecture no longer meets its contract.
- Use the simpler native boundary unless a specialized system has demonstrated benefits that justify its extra operational and consistency cost.