Introduction
A growing shop needs two useful order views: the oldest ready orders at one shop, and one customer's newest order history. The first page contains seven orders. Separate order IDs must stay separate even when timestamps and prices match; a zero-cent promotional order can still be ready for pickup.
You will save two reusable MySQL queries and an index script, apply useful indexes, and keep both first pages within a shared budget of two secondary indexes and 64 native diagnostic row requests per page. The original synthetic snapshot has more than 96,000 orders. This unit tunes first pages; the next independent unit teaches complete cursor pagination.
You need basic terminal navigation, MySQL connections and SELECT/WHERE. Parameters, half-open time ranges, ordering, indexes and actual-plan observations are explained before use. The private MySQL server, original data and Terminal are ready; no Python or frontend development is required.
Work in /home/labex/project/order-desk. Create or edit queue.sql, history.sql and indexes.sql, and manage at most two secondary indexes. Preserve the original orders, columns, primary key, identities, states, exact application timestamps, cents, notes and executable tools. Do not delete or rewrite source rows, change diagnostic settings, add optimizer hints or use an external cache. Index names and equivalent SQL may differ. Runtime labels and outputs remain English.
The prepared diagnostic connections disable index condition pushdown, an engine optimization that can hide filtering inside the storage engine. This makes native row-request comparisons easier to interpret. It applies only to these connections; the exercise does not measure physical disk I/O or promise a production speed multiplier. Milliseconds and one exact plan string are not acceptance targets.
Read the Workload and Tune the Pickup Queue
In this step, you will inspect valid orders before choosing a useful index. A faster first page must still contain the same required order facts.
Start in Terminal at the normal shell. cd enters the workspace; ./ runs a program in the current directory. db opens the prepared MySQL client for reading orders and managing course indexes. A here-document sends the lines between the quoted SQL marker and its closing SQL to that client. The closing marker stands alone. SQL statements end with semicolons, and this client connection closes when the block ends. If a path or command name is mistyped, use ls to inspect the real names and retry the corrected command; keep the original data intact.
Inspect shop 3's ready orders on May 10. The comparison >= includes the opening; < excludes the next midnight. ORDER BY placed_at,order_id sorts by time and then ID, in ascending order. The ID breaks timestamp ties. total_cents stores integer cents, and placed_at is a DATETIME(6): an application timestamp retaining six microsecond digits. These supplied timestamps do not require time-zone conversion.
cd /home/labex/project/order-desk
./db <<'SQL'
SELECT order_id,shop_id,customer_id,state,placed_at,total_cents
FROM orders
WHERE shop_id=3 AND state='ready'
AND placed_at>='2025-05-10' AND placed_at<'2025-05-11'
ORDER BY placed_at,order_id;
SQL
There are ten eligible orders: nine distinct IDs share 09:15, and order 900021 occurs at 23:59:59.999999. Order 900004 costs zero and is still ready. Most of the equal-clock orders cost 1250 cents, but equal price is not identity. The exact next midnight belongs to the next day. Keep these facts while changing access paths.
An index is an additional ordered structure that helps MySQL locate rows. It costs storage and maintenance on writes, so adding every possible index is not the goal. A composite index orders entries by several columns. The primary key already identifies order_id; this desk permits no more than two additional, secondary indexes across both workloads.
A plan describes how MySQL reaches a result. Ordinary EXPLAIN includes estimates. EXPLAIN ANALYZE actually executes the SELECT and reports iterator observations such as actual ... rows=... loops=.... FORMAT=TREE displays execution steps, called iterators, as indented parent/child text. LIMIT 7 requests at most seven ordered rows. That top iterator can return seven rows while a lower table-scan iterator reads a much larger set. Its elapsed times vary and are not graded. DATE(placed_at) below takes the calendar date, so this particular baseline request still selects the right single day.
./db <<'SQL'
EXPLAIN ANALYZE FORMAT=TREE
SELECT order_id,shop_id,customer_id,state,placed_at,total_cents
FROM orders
WHERE shop_id=3 AND state='ready' AND DATE(placed_at)='2025-05-10'
ORDER BY placed_at,order_id
LIMIT 7;
SQL
Read the lower access iterator, not only the seven rows at the top. Without a suitable index it scans roughly 96,000 orders. The diagnostic connection leaves index condition pushdown off so filtering remains visible; keep that prepared comparison setting. A small returned page alone does not establish a small amount of database work.
Save a query that can handle different shop/state requests and arbitrary half-open time ranges. Prefixing a name with @ makes it a native MySQL session variable. The desk supplies @group_id, @state, @from_at and @until_at for each request in that query's connection. A variable set in one manual db connection does not persist into another. Keep SET statements outside the saved query file.
The two query interfaces return six columns in this order: order_id, group_id, actor_id, state, event_at, amount_cents. AS gives a selected column the interface name: the shop is the group and the customer is the actor. Preserve the real values rather than replacing them with summaries. LIMIT 7 is the desk's seven-order page contract; reducing it would hide orders rather than optimize the page. ASC makes ascending order explicit.
cat > queue.sql writes the following complete text to that file, replacing any previous content. The quoted here-document marker prevents shell expansion. Paste the whole block, with the closing marker alone:
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
ORDER BY placed_at ASC,order_id ASC
LIMIT 7;
SQL
./desk page queue
page runs your saved SQL unchanged and displays its actual rows. The first page contains IDs 900001 through 900007, including the zero-cent order. The native handler_requests value counts requests at the server/storage-engine boundary in this diagnostic connection; it is not a physical I/O count. It remains large before indexing. The displayed next-request cursor comes only from the actual last returned row; this first-page query does not yet use it. Cursor inputs shown as null mean there is no previous row on a first-page request.
A useful access prefix fixes shop and state first, then follows the requested time range and (time,ID) order. The two equality columns can exchange positions for these queries; their textual order in WHERE does not dictate index order. A time-only index may still sift through unrelated shops or states on other requests. Save the selected candidate in indexes.sql so it can also be applied to a new copy.
cat > indexes.sql <<'SQL'
CREATE INDEX queue_time ON orders(shop_id,state,placed_at,order_id);
SQL
./db < indexes.sql
< supplies a file as the client's SQL input. This creates an access structure, not new orders. If you rerun the same CREATE INDEX later, MySQL reports a duplicate key name because that index is already present. Inspect existing indexes before deciding whether to keep or replace a course-created candidate; do not delete source orders or expect this script to be an idempotent migration.
Here -e executes the complete quoted SQL string and exits. The desk's plan action executes EXPLAIN ANALYZE on the actual saved query. --from and --until select another opening and exclusive end for this request; date-only values mean midnight.
./db -e "SHOW INDEX FROM orders;"
./desk page queue
./desk plan queue
./desk page queue --from 2025-01-05 --until 2025-01-12
The access path should now use a narrow indexed range, while the original May 10 page still contains the same seven order IDs and exact fields. Check both requests against the 64-request budget. SHOW INDEX proves existence; the actual plan, returned rows and request count establish the useful behavior. Exact index names, costs and milliseconds may differ.
If a command or filename is mistyped, read the error, use ls to inspect the workspace, and retry the corrected name. Order desk error means a file, SQL or connection error; it is not a successful empty result. Fix the saved SQL or candidate index, then rerun the real request. Run the step check after confirming both correctness and observed work.
Share the Index Budget with Customer History
In this step, you will support a second business request without losing the pickup queue or exceeding two secondary indexes.
Stay in Terminal and /home/labex/project/order-desk. Keep the original tables and queue.sql. The history view selects one customer, regardless of pickup state or shop, and shows newest orders first. DESC reverses ordering; use it for both timestamp and ID so equal-time orders have a deterministic newest-first order. The native @actor_id parameter identifies the customer. Do not replace it with a fixed customer or add the queue's state restriction.
Write the complete first-page history query. It uses the same six-column interface and half-open range as the pickup query:
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
ORDER BY placed_at DESC,order_id DESC
LIMIT 7;
SQL
./desk page history
./desk plan history
The May 10 history starts with order 900021 at the last microsecond, followed by the equal-clock IDs in descending order. Their real cents and states remain visible. This correct first page can still require too much work: the existing queue index begins with shop/state, which history does not constrain.
A second useful prefix starts with customer and then time/ID. An ascending index can be scanned backward for the descending history order; the observed plan shows what actually happens. Extra covering columns are not required by this task. Keep no more than two secondary indexes and retain useful access for both workloads.
cat >> indexes.sql appends the new statement to the existing source file; > would replace it. After appending, execute only the new CREATE statement below, because the queue index already exists in this VM. On a new database the two-statement saved file is the complete index source.
cat >> indexes.sql <<'SQL'
CREATE INDEX actor_time ON orders(customer_id,placed_at,order_id);
SQL
./db -e "CREATE INDEX actor_time ON orders(customer_id,placed_at,order_id);"
./db -e "SHOW INDEX FROM orders;"
./desk page history
./desk plan history
Check a broader customer history, another identity, an empty date range and the original queue. --actor changes the requested customer ID; it does not change source data. A valid query can return no rows, which differs from an execution error.
./desk page history --from 2025-01-01 --until 2025-05-01
./desk page history --actor 78
./desk page history --from 2027-01-01 --until 2027-01-02
./desk page queue
Both workloads must retain correct first pages within the diagnostic budget. The empty request honestly returns an empty row list, not invented orders. The saved indexes.sql and the live definitions are checked independently: a good file does not prove it was applied, and a working live index does not replace the saved source.
If you change a candidate, inspect SHOW INDEX, drop only the course-created index you are replacing, update indexes.sql and apply the new definition. Never drop the original primary key, columns or rows. A repeated CREATE's duplicate-name error is a reason to inspect the current definition, not a reason to reset the VM.
This unit's endpoint is two useful first-page lookups. Seven returned rows are not a complete longer queue. The next independent unit starts with these first-page queries and indexes already prepared, then teaches cursor conditions that keep every order across pages. Run the final check with both saved queries, the complete index script and at most two effective secondary indexes.
Summary
You inspected real order facts and actual execution observations, saved reusable half-open pickup and customer-history queries, and supported both first pages with a limited set of useful indexes. Original identities, timestamps, cents and notes remain intact. You distinguished native diagnostic row requests from returned row counts, estimates and physical I/O. Full multi-page delivery is the next unit’s goal.
