By James Harvey
Early-stage startup problems
- Finding customers
- Rapid feature development
- Iterating to find product-market fit
- Fixing bugs and bad product decisions
Scaling startup problems
- All that stuff from before
- Heaps of customers
- A bunch of really big customers
- Even bigger customers coming down the pipeline
- Your database servers go to 100% CPU randomly
- You start getting weird errors from the database
You will make mistakes in your database design
You will think you know how MySQL will behave at scale and you will be wrong
It is a good thing when you start hitting scaling issues in your app because it means people are actually using it!
Making maximum use of each MySQL node
Adding more MySQL nodes
Requirement 1
Grab the shifts that start on a given date
Please explain!
MySQL is conducting a full table scan to find the shifts
Getting better
But we can still take this further
The culprit
MySQL can't make full use of the index when a function is used.
Let's get rid of the function
Requirement 2
Grab the shifts end time as well
Why did we go from nothing to 140ms?
Requirement 3
Search on the end time
The order of the index matters!
Composite indexes are basically stored as a concatenated string.
Does join order matter?
No, but MySQL can only pick one index to use.
Let's try another index
James Harvey
Chief Software Architect - foundU