How I cut API response times by 40%

When I took over the telehealth API at Spring, p95 latency was sitting around 1.4 seconds. Most of it was MySQL. The rest was PHP doing work it didn't need to do. We got it under 800 ms in six weeks without touching the schema or switching databases.

The techniques aren't exotic. They're the same ones that fix 80% of slow APIs: instrument first, index correctly, stop querying the same row twice, and pool connections. Here's exactly what we did.

Start with EXPLAIN, not intuition

Every engineer has a hunch about which query is slow. Most hunches are wrong. I logged every query over 200 ms for a week using MySQL's slow query log, then ran EXPLAIN ANALYZE on the top offenders. Three of them were doing full table scans on a 12M row appointments table — type: ALL in the output, which is the bad kind.

Setting up slow query logging

Add these to your my.cnf and restart: slow_query_log = 1, slow_query_log_file = /var/log/mysql/slow.log, long_query_time = 0.2. Then use pt-query-digest from Percona Toolkit to aggregate the log into a ranked report. Within five minutes you'll know which three queries are responsible for 70% of the pain.

The indexing pass

Once I had the culprit queries, the fixes were mostly indexing. Adding composite indexes on (doctor_id, scheduled_at) and (patient_id, status) cut the worst queries from 900 ms to under 40 ms. The key insight is column order: put the equality condition first, the range condition second.

Composite indexes vs single-column

A lot of databases have a single index on doctor_id and a separate one on scheduled_at. MySQL's optimizer will use one of them, not both. A composite (doctor_id, scheduled_at) index lets the query filter on doctor first — shrinking the result set to one doctor's rows — then scan only that subset ordered by date. That's often a 20× improvement over either single-column index.

Dropping dead indexes

I also dropped five unused indexes. Every index costs write performance — MySQL updates all indexes on every INSERT and UPDATE. The sys.schema_unused_indexes view shows indexes that haven't been used since the last restart. Any index with zero reads over a live week is a candidate for removal.

Fixing N+1 queries

The indexing pass took us to around 600 ms p95. The remaining work was PHP. The most common pattern I found was N+1 queries — a query returning N rows, followed by N separate queries to fetch a related row for each one.

In our case: an endpoint fetched 50 appointments, then for each appointment fired a separate query to fetch the doctor's name. 50 appointments meant 51 queries. The fix was to collect the doctor_id values from the first query, run one WHERE doctor_id IN (...) query, and hydrate from a map. 51 queries became 2.

• Identify N+1s: log every query in a single request and look for repeated query shapes with different IDs.
• Fix with IN queries: collect the IDs from the first query, batch-fetch related rows in one round trip.
• Use eager loading: Eloquent, Doctrine, and ActiveRecord all have eager-loading APIs — use them explicitly for known relationships.
• Cap IN-clause size: batch IDs in chunks of 200–500; very large IN clauses degrade query planning.

The service layer

The real surprise was the PHP service layer. One endpoint was running the same user-lookup query four times across nested method calls. Each layer called UserService::find($id) independently, assuming someone else had already cached it.

Request-scoped caching

The fix was a request-scoped identity map: a simple array keyed by user ID that the service checked before hitting the database. Because it lives on the service object and the service is a singleton for the lifetime of the request, it resets automatically on the next request. No external cache, no invalidation logic, no TTL to manage. The p95 dropped another 200 ms from that change alone.

The pattern generalises. Any object looked up by ID more than once per request is a candidate for request-scoped caching. Role lookups, feature flags, configuration values — all of these get queried repeatedly in typical PHP apps.

Connection pooling

PHP's traditional model is one MySQL connection per request, opened at the start and closed at the end. On a server with 200 concurrent requests, that's 200 connection handshakes per second. At p95 scale, that overhead adds up — around 8 ms per request on our setup.

We added ProxySQL in front of MySQL. It maintains a pool of persistent connections to MySQL and hands them off to PHP processes on demand. Connection establishment time dropped from ~8 ms to under 1 ms. For a 150 ms API endpoint, 8 ms per connection is meaningful.

Caching hot reads with Redis

After all of the above, we were at 420 ms p95 — already a 70% improvement. The last push came from caching reference data that never changes between deployments: specialty lists, clinic locations, procedure codes. These were being queried on every request because they live in the database.

We cached them in Redis with a 10-minute TTL and a manual flush on deploy. The queries went from ~12 ms each to sub-millisecond. Rule of thumb: any table a developer would hesitate to UPDATE in production is a caching candidate.

Monitoring what you improved

The work isn't done until you can see it in a dashboard. We set up three metrics: p50 and p95 latency per endpoint (via application-level timing middleware), slow query count per minute (from the MySQL slow log), and cache hit rate (from Redis INFO stats). If any of those regress on a deploy, we catch it in the first five minutes.

What I'd do differently

I'd set up slow query logging and endpoint-level latency tracking on day one. Half the work here was finding the problems, not fixing them. In a fresh project I'd also enforce a SELECT * ban from the start — most of the heaviest queries were selecting 20 columns when they needed 3. If the API you're optimising handles patient data, there are HIPAA constraints around caching and audit logging worth knowing before you reach for Redis.

FAQ: MySQL and API performance

Working on a slow API?

If your backend has latency you can't explain — or you've inherited a codebase with no instrumentation — I'm available for performance audits and engineering engagements. I've optimised APIs in PHP, Node.js, and Python across healthcare, ed-tech, and rentals products.