Introduction
Your colleague has already tuned the shop's two first-page lookups. The pickup queue is oldest-first, and customer history is newest-first. A caller now needs to continue past the first seven orders. Several real orders share one timestamp, so a timestamp-only cursor can lose records or repeat them.
This independent unit provides its own MySQL snapshot, working first-page queue.sql and history.sql, applied indexes and indexes.sql. It needs no files from the previous VM. You will edit the two saved queries so every original order survives complete ascending and descending page walks, while keeping efficient indexed access.
Prerequisites are the first unit's MySQL parameters, half-open bounds, two-column ordering and diagnostic plans. Nullable cursors, their complete comparisons and deep-page checks are taught here. Use Terminal and /home/labex/project/order-desk. Change only queue.sql and history.sql; preserve supplied indexes/index source, all order facts, columns, primary key and executable tools.
Each page still contains up to seven actual orders and uses at most 64 native diagnostic row requests. The prepared connection keeps index condition pushdown off for this controlled comparison; keep that setting and avoid optimizer hints. Requests use a fixed data snapshot. No concurrent-update snapshot, physical-I/O count or production latency guarantee is claimed. Runtime labels and outputs remain English.
Carry Both Keys into the Next Pickup Page
In this step, you will advance the oldest-first queue without losing the remaining orders at the same timestamp.
Start in Terminal at the normal shell. The provided db and desk are the same interfaces practiced in the first unit. If a path or filename is wrong, use ls, check the actual name and retry without changing original rows. cat prints the saved query text here. The following page request reads its actual output; no reference query repairs missing rows.
cd /home/labex/project/order-desk
cat queue.sql
./desk page queue
The saved query has the useful shop/state/time conditions and (placed_at,order_id) ordering, but no cursor condition. Its first page correctly returns 900001 through 900007. The desk's cursor_for_next_request contains the actual last row's time and ID. It identifies where a next request can begin; it does not promise another nonempty page.
--after takes two arguments: the last actual timestamp and the last actual order ID. Quotes keep the timestamp's space inside one shell argument. Request the next page using the cursor you just observed:
./desk page queue --after "2025-05-10 09:15:00.000000" 900007
The unchanged starter query returns the first seven IDs again because it ignores that cursor. All commands executed successfully, but this is the wrong next page. Do not change the source timestamps or deduplicate a displayed list to conceal the problem.
The desk supplies two more native session variables: @after_at and @after_id. For the first page, both are NULL, meaning no prior cursor. SQL tests that absence with IS NULL, not = NULL. Otherwise an oldest-first next row must have a later timestamp, or the same timestamp and a larger ID. Using only placed_at > @after_at would lose IDs 900008 and 900009 at the repeated clock reading.
Keep the existing group, state and half-open bounds. Put the cursor choices inside parentheses, joined to those filters by AND; OR must not release a row from the other business conditions. The order remains ascending by both keys. cat > queue.sql replaces the complete query below; the quoted SQL here-document marker prevents shell expansion and closes on its own line.
cat > queue.sql <<'SQL'
SELECT order_id,shop_id AS group_id,customer_id AS actor_id,
state,placed_at AS event_at,total_cents AS amount_cents
FROM orders
WHERE shop_id=@group_id AND state=@state
AND placed_at>=@from_at AND placed_at<@until_at
AND (@after_at IS NULL
OR placed_at>@after_at
OR (placed_at=@after_at AND order_id>@after_id))
ORDER BY placed_at ASC,order_id ASC
LIMIT 7;
SQL
./desk page queue
./desk page queue --after "2025-05-10 09:15:00.000000" 900007
./desk plan queue --after "2025-05-10 09:15:00.000000" 900007
The first page stays unchanged. The next page now contains 900008, 900009 and 900021, with their original times and cents. The exact next midnight is still outside the requested day; the last microsecond remains inside. The plan should reach the cursor's indexed range instead of reading and discarding every earlier order. Inspect the actual rows and the diagnostic count together.
The walk action repeatedly runs this same saved query, passing the actual last row's cursor, until a real empty page appears. It retains returned order and fields exactly; it does not sort, deduplicate or refill results. If a query repeats its last cursor, the tool reports an error rather than presenting a partial walk as complete.
./desk walk queue
./desk page queue --after "2025-05-10 23:59:59.999999" 900021
The walk delivers all ten orders exactly once: seven, then three, then an empty page. The explicit last-row cursor also returns an empty row list. That is a valid endpoint, unlike an Order desk error. If a file or SQL is wrong, use ls or cat to inspect it, correct the saved query and rerun the actual request. Do not alter provided indexes or original rows. Run the step check after confirming the complete queue.
Continue Newest-First History with Bounded Work
In this step, you will complete the opposite cursor direction and compare a deep page with an offset-based read of the same data.
Stay in Terminal and /home/labex/project/order-desk. Keep the corrected pickup query and the prepared indexes/index source. The customer history uses DESC for both time and ID, so a next row has an earlier time, or the same time and a smaller ID. Its customer filter and half-open range stay in force. The first-page NULL case remains the same.
Replace the complete history query. Do not add a ready-state restriction: customer history includes all original states and shops for that customer.
cat > history.sql <<'SQL'
SELECT order_id,shop_id AS group_id,customer_id AS actor_id,
state,placed_at AS event_at,total_cents AS amount_cents
FROM orders
WHERE customer_id=@actor_id
AND placed_at>=@from_at AND placed_at<@until_at
AND (@after_at IS NULL
OR placed_at<@after_at
OR (placed_at=@after_at AND order_id<@after_id))
ORDER BY placed_at DESC,order_id DESC
LIMIT 7;
SQL
./desk page history
./desk page history --after "2025-05-10 09:15:00.000000" 900004
./desk walk history
The first page begins with 900021, then 900009 down to 900004. Its next page contains 900003, 900002 and 900001. The complete walk retains all ten orders and then reaches an actual empty page. The zero-cent order remains in the first page; equal time or price never merges identities.
A deep OFFSET asks MySQL to discard preceding rows after ordering. It may return seven correct rows while reading many more. A cursor can start near the last actual key instead. Compare both on customer 77's January-through-April history, whose exclusive end is May 1.
In the native client below, LIMIT 1 OFFSET 199 skips 199 ordered rows and returns the 200th row, the last one preceding an OFFSET 200 page. EXPLAIN ANALYZE actually runs the offset query and reports its iterator work. The final SELECT displays that page's real rows. Here-documents and both sorting directions are the same as already practiced.
./db <<'SQL'
SELECT order_id,placed_at
FROM orders
WHERE customer_id=77
AND placed_at>='2025-01-01' AND placed_at<'2025-05-01'
ORDER BY placed_at DESC,order_id DESC
LIMIT 1 OFFSET 199;
EXPLAIN ANALYZE FORMAT=TREE
SELECT order_id,shop_id AS group_id,customer_id AS actor_id,
state,placed_at AS event_at,total_cents AS amount_cents
FROM orders
WHERE customer_id=77
AND placed_at>='2025-01-01' AND placed_at<'2025-05-01'
ORDER BY placed_at DESC,order_id DESC
LIMIT 7 OFFSET 200;
SELECT order_id,shop_id AS group_id,customer_id AS actor_id,
state,placed_at AS event_at,total_cents AS amount_cents
FROM orders
WHERE customer_id=77
AND placed_at>='2025-01-01' AND placed_at<'2025-05-01'
ORDER BY placed_at DESC,order_id DESC
LIMIT 7 OFFSET 200;
SQL
The 200th row is order 94263 at 2025-02-25 02:30:00.000000. Read the lower iterator's actual rows: the offset request must pass over the first 200 before delivering seven. Now use that actual row as the saved query's cursor, with the same customer and bounds. --from and --until apply to this invocation only.
./desk page history --from 2025-01-01 --until 2025-05-01 --after "2025-02-25 02:30:00.000000" 94263
./desk plan history --from 2025-01-01 --until 2025-05-01 --after "2025-02-25 02:30:00.000000" 94263
The same seven IDs appear, beginning with 3542 and 87067. The cursor query should reach the indexed ranges with a small actual request count, rather than discarding 200 earlier rows. Compare work on the same page, not two unrelated requests; elapsed milliseconds still do not establish the acceptance result.
Finally check another customer, a valid empty range and the unchanged pickup walk:
./desk page history --actor 78
./desk page history --from 2027-01-01 --until 2027-01-02
./desk walk queue
Keep both reusable SQL files. The supplied indexes and indexes.sql remain intact. A query's SQL/file/connection error stays an error; the reader does not invent missing orders. These are stable walks over the prepared fixed snapshot, not a transaction snapshot during concurrent changes. The final check verifies exact original fields/order, small complete walks, changed identities/ranges/cursors and diagnostic work, together with preservation of the source and tools.
Summary
You continued two indexed order views through complete timestamp-and-ID cursors, preserved every equal-time order and zero-cent value, and verified real empty endpoints. You compared the same deep page through OFFSET and indexed cursor requests without treating milliseconds as proof. Both directions work on the fixed snapshot while the original data, tools and prepared indexes remain intact.
