DB Performance Improvement Phase 1 — Moving from Ad-hoc Fixes to Measurement-First Operations

A stage of tasteck's database improvement work is now complete. In this article, for both operators and developers, we explain (1) the 7 index additions we applied to production today, (2) the "ad-hoc fix trap" we faced along the way and the measurement-first policy we switched to after consulting industry case studies and papers, and (3) the point history screen for the guest mobile app we released at the same time.

What we did — the contents of Phase 1

We added the following 7 composite indexes to tasteck's production MySQL (8.0.45).

Table	Index	Purpose
call_histories	(company_id, created_at)	Descending retrieval of the incoming call history list
call_histories	(company_id, guest_id)	Searching calls per guest
guest_notes	(company_id, cast_name_id, updated_at)	Guest notes per cast member
guest_notes	(company_id, guest_id)	Guest notes per guest
guest_points	(company_id, guest_id, shop_id)	Point balance calculation
orders	(cast_name_id, status, plan_in_time)	Order list per cast member
orders	(shop_id, status, plan_in_time)	Venue filtering for the guest calendar

The orders table has 1,424,125 rows, so index creation takes a fair amount of processing, but with MySQL 8.0's ALGORITHM=INPLACE we could apply it with no production downtime. The actual production apply time was 70 seconds in total for 5 of the indexes.

After applying, we confirmed with EXPLAIN that the key queries flow to range scans + the new indexes as expected. The biggest impact was on the guest mobile calendar screen: previously it could only use the single-column shop_id FK index and a filesort occurred, but with the (shop_id, status, plan_in_time) composite index, processing now completes with index condition pushdown + an already-sorted index.

The half year we could not escape "ad-hoc fixes"

Let us be honest. Until we added these 7 indexes, tasteck kept repeating symptom-driven partial optimization.

The flow was always the same.

1. A user reports "screen X is slow"
2. We identify the API involved and read the SQL
3. We add an index tailored to that single query
4. When the next slow screen appears, we add yet another index

If you keep doing this, the outcome is predictable.

• Only visible slowness gets fixed. Only the places where users spoke up get improved
• No global optimization. Because each index is added for that one case, other similar cases are not covered
• It never ends. Fix one thing, and a hidden slow spot becomes visible

For half a year, we were stuck in this structure.

We re-read the papers and industry case studies

Behind the decision to add all 7 indexes at once was a re-reading of industry case studies and academic papers. Specifically, we consulted the following.

• Shopify Pods Architecture — stayed on a single MySQL until 2014
• Partitioning GitHub's relational databases to handle scale — a single mysql1 cluster for more than 10 years
• Herding elephants: lessons learned from sharding Postgres at Notion — sharded into 480 logical / 32 physical shards by 2020
• How Figma's Databases Team Lived to Tell the Scale — stayed on a single Postgres + Replica until 2022
• Guide to scaling your database — PlanetScale — the line to consider sharding is a working set of 500GB–1TB
• Brent Ozar "How Many Indexes Are Too Many?" — guideline of 8 indexes or fewer per table; write/read over 10× is a deletion candidate
• Markus Winand "Use The Index, Luke" — leftmost prefix matters most; putting the most selective column first is a myth

From this, tasteck's current position became clear.

tasteck's scale (~100 companies, 1.3 million orders rows, a single RDS + a single EC2) is close to where Shopify was around 2010, GitHub around 2015, and Notion around 2018.

In other words, we are at a stage where a single MySQL can still carry us for several more years. With this recognized, the order of what to do became clear.

1. 計測基盤の導入 ← 最優先・まだやれていない
2. Index 健康診断 ← Phase 1 で前払い
3. Read Replica 活用 ← 次にコスパが高い
4. 様子見 → 必要なら垂直分割 or sharding (2 年後以降)

(1. Introduce a measurement platform ← top priority, not done yet / 2. Index health check ← prepaid in Phase 1 / 3. Use a Read Replica ← next best cost-performance / 4. Watch and wait → vertical partitioning or sharding only if needed, 2+ years from now)

Sharding, at the bottom, is the area where Shopify, Notion, and Figma all look back and say they should have done it later. Touching it at tasteck's scale would clearly be over-investment. On the other hand, all of these companies say they should have introduced a Read Replica earlier. We rebuilt our priorities with this contrast in mind.

Positioning Phase 1 as a "prepayment"

Ideally, we should first build the measurement platform (Phase 0), identify the top 20 queries by fingerprint, and only then add indexes.

However, half a year of operations had already made it clear which queries were slow. So we set a self-imposed rule — "this is the last time we add indexes without a measurement platform" — and applied the 7 indexes up front as a response to the known major slow queries. We judge this a prepayment that can be justified.

The Brent Ozar guideline (8 indexes or fewer per table) is also satisfied, and EXPLAIN has confirmed that the added indexes are used with real data. If any of them turn out to be unused, they can be removed via sys.schema_unused_indexes.

Starting the Phase 0 measurement platform — running in QA from today

On the same day, we created a custom Parameter Group on the QA RDS and applied the following.

slow_query_log  = 1
long_query_time = 0.5
log_output      = TABLE   # mysql.slow_log テーブルに直接書き込み

The key point is log_output = TABLE, which makes immediate SQL analysis possible with SELECT * FROM mysql.slow_log. There is no need to download log files as with pt-query-digest. We plan to apply the same settings to the production RDS soon (it requires a 60–120 second reboot, so it will be done in the late-night window).

On top of that, we wrote a fingerprint aggregation script (a lightweight version of pt-query-digest). The script normalizes numbers, quoted strings, and IN clauses, groups SQL with the same pattern, and sorts descending by Total Time / Avg Time / Rows Examined. By looking at this report monthly, we can always see "the top 20 heaviest queries in tasteck right now".

Until this Phase 0 is in place, our rule is that further index additions are forbidden in principle. We believe this is the most reliable way to escape the ad-hoc fix trap.

Outlook for Phase 2 and beyond

With Phase 1 (indexes) and Phase 0 (measurement) in place, the next step is introducing a Read Replica.

• Add 1 RDS Read Replica (roughly ¥30,000–50,000 per month)
• Switch the DB connection of the analytics EC2 (prd-notel-analytics) to the Replica
• Point read-only paths in the guest mobile app (calendar, point balance) to the Replica as well

We estimate this alone lowers read load on the Primary by 40–60%. Analytics queries are the heaviest, so physically separating them has a large impact. It is also the road Shopify, Notion, and Figma took before sharding, and at tasteck's scale we consider it the measure with the best cost-performance.

Conversely, here is what we will not do — the following 3 are frozen for 2 years as of now.

• Sharding — over-investment with a working set < 500GB; we would certainly regret it
• Full CQRS / Event Sourcing — at a 100-company scale, the operational burden exceeds the benefit
• Migrating away from MySQL — staying in the Percona Toolkit / Vitess ecosystem keeps more future options open

Bonus: we added a point history screen

That was a lot of technical talk, but today there is also one update on the user experience side. We added a point grant/usage history to the points screen of the guest mobile app (guest.no-tel.com).

Previously, the points screen showed only the total balance and per-venue balances. There was no way to see how those points came to be, so questions like "When and why were these points granted?" and "Which reservation was this usage for?" could not be answered.

The new screen (/point/history) is designed with the same interaction pattern as the usage history page.

• A list in descending date order (grants in green +100 pt, usage in red -500 pt, easy to see at a glance)
• A venue filter (a select box, for guests who use multiple venues)
• Pagination with a "show more" button, 20 items at a time
• Staff operational notes (remarks) are internal, so they are hidden

For the UI layout, we chose a separate page instead of a dropdown. This prioritizes list visibility when the number of venues grows and the usability of the OS-native picker on mobile.

On the backend, we created 3 new endpoints for history retrieval.

GET /api/guest/guestPoint/findAllHistory/:email?limit&offset&shopId
GET /api/guest/guestPoint/countHistory/:email?shopId
GET /api/guest/guestPoint/findHistoryShops/:email

These APIs use the IDX_guest_point_company_guest_shop index added this time, so responses are fast.

Summary

• Phase 1 complete: applied 7 indexes to production orders with no downtime; key queries now use range scans
• Policy shift: re-evaluated tasteck's current position using papers + industry case studies, and moved to measurement-first operations
• Phase 0 started: slow_query_log + the fingerprint aggregation script are running in QA
• Next moves: roll out slow_log to production → consider a Read Replica → keep sharding frozen for 2 years
• For users: added a point grant/usage history screen to the guest mobile app

Given tasteck's positioning as an "industry-focused micro SaaS", we believe that steady measurement and health improvement, rather than flashy technology choices, is what becomes our strength. We will keep working at this pace.