In a relational MySQL structure with vehicles, owners, workshops, jobs and damage records hanging off them.
When the volume itself is the job
A few million rows are not a problem. They become one when they have to be queried across six relations, updated every night and answer in under a second. That is where I get called in.
FIG. 01
Scale
What I have had my hands on
These are from systems I built and ran myself. They are not numbers from a test database.
1.2 million of them synonyms. Another 1.5 million images are attached to the same taxonomic hierarchy.
On a single sales type. Quotes, orders, invoices and credit notes each have their own of the same size.
FIG. 02
Relations
The row count is rarely the hard part
A table with twenty million rows is not a problem in itself. It becomes one when an ordinary query has to cross six relations to produce an answer, and when all six of those tables have grown too.
This is what I have worked with for years. Taxonomic hierarchies with synonyms at every level. Vehicles with VINs, number plates, owners, jobs and damage records. Sales data where a single line hangs off a customer, a department, a product and the finance system at the same time.
ZiiX
- Number plate
- Vehicle
- User
- Job
- Damage
PlantGeekz
- Image
- Species
- Genus
- Family
- Order
Skorstensgaard
- Invoice line
- Order
- Invoice
- Customer
- Department
Three chains from systems I have built. Every link is a relation the query has to cross before it can answer.
FIG. 03
Consequences
What it costs to lose your grip on it
None of this happens on day one. It happens once the data has grown quietly for three years and nobody has looked at the model since it was drawn.
Two numbers for the same thing
Sales and finance calculate differently because there are two places to get the figure. Meetings go on deciding who is right instead of making the decision.
Duplicates that cost money
The same customer three times, the same vehicle twice. Discounts get applied wrongly, and reminders go out to people who already paid.
Slow enough that people stop
A query that took 200 milliseconds at a hundred thousand rows takes forty seconds at ten million. So people build their own spreadsheet on the side, and you are back where you started.
The night job does not finish
The import has to be done by seven. At that volume it takes nine hours, so people arrive to yesterday's numbers without knowing it.
Nobody dares change anything
A schema change on twenty million rows locks the table while it runs. So the change gets postponed, and postponed again, and eventually the system has set solid.
You cannot delete what you must
GDPR requires that you can remove a person from your systems. Once nobody knows what is connected to what, nobody dares press the button either.
FIG. 04
The craft
What I do about it
Data modelling
The relations laid out properly from the start, normalised exactly as far as it makes sense. Sometimes the right call is to duplicate on purpose, and then it should be a decision rather than an accident.
Indexing and queries
Reading a query plan and seeing why MySQL picked the wrong index. That is often the difference between forty seconds and forty milliseconds.
Migrations without downtime
Schema changes on tables with millions of rows, without closing the shop while it happens.
Search off to the side
Meilisearch and Sphinxsearch take the full-text work so the database can do what it is good at.
Jobs that can be restarted
Batch and background runs in chunks with checkpoints, so a failure at three in the morning does not cost the whole import.
Monitoring and restores
Zabbix on the things that matter, so you find out before your users do. And a backup somebody has actually rolled back and timed.
Got something that needs building properly?
Write a few lines about what you are dealing with. I will come back with what I think, including if the answer is that someone else would do it better.