The endpoint is slow. Should we split the service?

Not from the feeling. Group the statements in the slow log by SQL text and write Rows_examined, Rows_sent and how many times the statement ran. A loop that became one aggregating query, or a missing index, is a different change from a new service boundary. Splitting the service first moves the same statement somewhere harder to read.

The user waited a long time and the slow log is almost empty. Is the database fine?

Not yet. The log only keeps statements over its threshold. A short query once per row can add up to the wait you felt and never appear. Count repetitions in the framework log, or lower the threshold on a copy. On one training platform the useful finding was that loop: one aggregating query then took 2.451 seconds instead of 12.41, and used 1.5 GB less memory. That is that operation, not a reason to ignore a quiet log.

Can those before-and-after numbers tell us how fast our query will get?

No. They describe operations on one training platform: the aggregating query, a weekly CPU average that fell from 82.2% to 2.75% once missing search indexes and the other bottlenecks were dealt with, a critical operation from 23,937 ms to 48 ms, an API response from 912 KB to 2.1 KB. Use the shape of the check. Write your own statement, your own row counts, your own seconds.

Should we move the slow statement onto a queue and refactor later?

Only if you already know the statement is not the cost. A worker that runs the same loop still examines the same rows. Fix the statement, or the index, or the columns you did not need. Move work off the request when the cost is a call that should not hold the page, and make the retry safe. Do not send a raw log that still contains customer values.

SQL: how to read the slow log before refactoring a service

How to read a slow query log so the refactor follows the statement, instead of the other way around.

The endpoint is slow, and the proposal is to split the service. A cache in front, a queue beside it, a new boundary because the current one looks embarrassing. The slow log is a list of statements that crossed a time threshold. It is not a verdict on the service. Refactor first and you move the statement into a place with worse logs and the same rows examined.

Read in this order before that refactor. Pick the wait a person already feels. Find the statements in that window. Group them by the SQL text. Compare rows examined with rows sent, and count repetitions. Only then decide: an index, one aggregating query instead of a loop, a smaller payload, or work that truly does not belong in the request. The numbers later belong to single operations on one training platform. They are not a multiplier for your service, and they did not come from splitting that platform apart.

1. What one log entry is actually saying

A MySQL slow log entry is a header and a statement. The header has a timestamp, the user and host, Query_time, Lock_time, Rows_sent and Rows_examined. Then the SQL. Query_time is how long that statement took, not how long the user waited. Lock_time is the part spent waiting on a lock. Rows_sent is what left the server toward the client. Rows_examined is what the server had to look at to produce that.

The ratio is the first cut. A statement that examines a few hundred thousand rows to send twenty is an access path problem: a missing index, a leading wildcard, a function wrapped around the column so the index cannot be used, a join that multiplies before it filters. That is not evidence that the service boundary is wrong. Splitting the service leaves the statement intact. You will read the same ratio in two repositories.

Lock_time close to Query_time means something else holds the row. A new service that takes the same locks adds a network hop. Find the other writer before you redraw the diagram.

Read a single entry with this in front of you:

Query_time, Lock_time, Rows_sent, Rows_examined, written next to the SQL, not remembered.
Whether Rows_examined is wildly larger than Rows_sent. That is the access path, until EXPLAIN says otherwise.
Whether Lock_time is a rounding error or a real share of Query_time.
The table and the predicate a human can point at. "The orders query" is not an entry. The SQL is the entry.

2. The log under-reports a loop

long_query_time is a threshold. A statement under it is invisible. A screen that runs a 200 millisecond query once per row can keep a person waiting for many seconds while the log stays quiet, if each execution sits under the threshold. If the user waited twelve seconds and the log shows one short statement, count the repetitions before you believe the request has been explained. The framework's query log, or a sample with the threshold lowered on a copy, shows the loop the slow log skipped.

On a training platform, "the platform is slow" was never a useful description. A loop of database queries became one aggregating query. That operation went from 12.41 seconds to 2.451 seconds, and memory use fell by 1.5 GB. The repetition was the bug. An aggregator that still examines the wrong rows would have kept the time. The refactor that would have missed it is the one that moves the loop into a worker and calls the endpoint fast.

Group the window by statement text, ignoring literals that change per row. Fifty lines that differ only by an id are one statement. Write the count next to Query_time. If the count is the problem, one aggregating query is the change. If a single execution examines far too many rows, the index or the predicate is the change. A service split is a third patch, and neither of those implies it.

The seconds a person waited, written down beside the log window. The log is not a substitute for that note.
Statements grouped by text. A loop is a count, not fifty incidents.
The threshold the log was using. If you do not know it, you do not know what the file omitted.
A copy where you can lower the threshold or read the framework's queries, so a fast-but-repeated statement can appear.

3. EXPLAIN on a copy, then the change

EXPLAIN belongs on a database with the same shape and a serious amount of data, and that database should not be serving customers. An empty schema will pick a different plan. Running the experiment on production is how you "just look" at the copy that takes orders. If the only MySQL you can read is the live one, take a replica or a restore first. Adding an index is a write. On a large table it is a long write.

Read the plan against the statement you already grouped. Note the access type, the key it used, and the rows it expects to examine. No key on the selective predicate is a candidate index. A key that still examines a huge number means the predicate does not match the key, or the statement asks for a sort or a leading wildcard the key cannot serve. Write that sentence down. An index that does not match it is a key nobody's statement uses.

Missing search indexes were part of the same training-platform work. Weekly average CPU load went from 82.2% to 2.75% after the bottlenecks, including those indexes, were addressed. That figure is a weekly average on that platform. It is not what a single EXPLAIN prints, and it is not a forecast for your CPU. The log and the plan told you where to look. The CPU moved because the statements stopped doing the wasteful thing, not because a service was renamed.

EXPLAIN for the grouped statement, on a copy with real volume, saved next to the SQL.
The key used, or the absence of one, written in a sentence you can review a week later.
Any new index is a named change with a before and after on the same statement. Not a bundle of indexes added because a blog listed them.
You are not learning the plan by altering the live server.

If a second environment does not exist yet, build the copy before the index. The surrounding decision, for the people who will feel the release, is do not find this out on production.

4. What the slow log will not justify

It will not justify a queue in front of a statement you have not read. A background job that runs the same loop still examines the same rows. The spinner becomes a "processing" badge. You moved the wait. A queue is the right boundary when the cost is work that should not hold the request, and a retry must not repeat the side effect. That is a different decision from hiding a slow query. Read the statement first. The boundary is in when a Node queue belongs beside Laravel.

It will not justify a cache of the slow result as the first move. You will store the shape the loop happened to return, and invent an invalidation story for a result you could have computed once. Cache the aggregating query after you have measured it. Do not cache instead of writing it.

The log also will not show the response the handler builds after the query returns. On that training platform a separate critical operation went from 23,937 ms to 48 ms, and one API response went from 912 KB to 2.1 KB. The response cut is not a slow-log line. The screen did not need the rest of the record. A refactor that keeps selecting every column ships the same payload across a new edge. Read the log, then read what the action returns. Both numbers stay attached to those operations. Neither means the platform became a fixed number of times faster.

It will not justify a framework rewrite. Inherited production systems were steadied by changing the query shape, the indexes and the payload. If the proposal is "the service is messy, so we rewrite", ask for the statement, the row counts and the seconds first. Messy code that runs one indexed query is maintenance. It is not today's incident.

No queue, cache or service split is proposed until the grouped statement has rows examined, rows sent and a count.
The handler's response size is a separate line. The slow log does not contain it.
A rewrite of the service is a later project with its own reason, not the implementation plan for this statement.

5. A reading order you can repeat

Pick the screen or the job a person already waits on. Write the seconds yourself. Pull the slow log for that window, and if the seconds do not add up, pull the statements the threshold hid. Group by SQL text. For the top statement, write Query_time, Lock_time, Rows_sent, Rows_examined and the repetition count. EXPLAIN it on a copy. Change one thing: the loop into one aggregating query, the missing index, or the columns you did not need. Measure the same operation again. Keep the log line next to the number.

Without the log fields, the first step is still name the screen before you redesign anything. If the wait is a CRM report, ask whether the rows belong to that customer: reports without a rewrite. A faster statement that returns another customer's rows is not the improvement.

One operation, before and after, with the SQL still identifiable.
The copy you explained against is named. So is the log threshold.
The refactor of the service, if it is still worth doing, starts after that note exists.

When to ask for a second look

You can group a log and write the row counts without a second person. Ask when the log is on a server nobody on the team can read, when EXPLAIN needs a production-sized copy you do not have, or when the statement sits inside a service you are about to replace and the replacement plan does not mention Rows_examined. Send the screen, the seconds, and the SQL with those counts. Do not send a raw log that still contains customer values. Strip the literals.

That reading, and the change that follows it, is work I do on systems that are already in production. Services cover performance work and backend systems. Selected work keeps each figure next to the operation that produced it: the aggregating query, the indexes, the response size. If you have the statement and the counts, message me on LinkedIn and send those. Leave the customer rows out.