Ask MySQL Questions in Plain English
Build MySQL operations and sales reports, inspect the generated SQL, and check joins, time zones, authentication and replica freshness before reusing the result.
By the Datarelix Team · Published · Updated · Last verified
An operations report can begin with a question as specific as “Which paid orders were due to ship before September 8 and still had no dispatch recorded at that cutoff?” Datarelix generates a read-only MySQL query and shows the rows that match. You need access to the order fields, an agreed cutoff and a definition of dispatch: the query is derived from your wording and the schema, as how text-to-SQL generates database queries explains, so those definitions decide what it returns.
Establish what a row means
In this example, each row in orders represents one order. ship_by is its promised dispatch date and shipped_at records actual dispatch. Both timestamps use the agreed reporting time zone. Cancelled orders are excluded.
SELECT order_id, ship_by, shipped_at
FROM orders
WHERE status <> 'cancelled'
AND paid_at < '2026-09-08 00:00:00'
AND ship_by < '2026-09-08 00:00:00'
AND (shipped_at IS NULL OR shipped_at >= '2026-09-08 00:00:00')
ORDER BY ship_by, order_id;
The cutoff is exclusive: an order due exactly at midnight September 8 is not overdue in this report. An order dispatched after that cutoff remains in the historical snapshot. If your database only stores today’s status and overwrites history, confirm which past states can actually be reconstructed.
| Order | Promised dispatch | Actual dispatch |
|---|---|---|
| 1042 | 2026-09-05 10:00 | Not recorded |
| 1056 | 2026-09-07 16:00 | 2026-09-08 09:00 |
Check a known delayed order and an on-time order before assigning work from the report.
Connect an approved MySQL source
Use approved connection details if you have them. Otherwise request the host, port, database, credentials and TLS requirements from the database owner. A dedicated account should have read access to the necessary tables and no unnecessary administrative permissions. The source must be reachable from Datarelix; MySQL is one of the supported sources.
The MySQL connection guide contains the setup fields and grants. Check the deployed MySQL version and authentication plugin. Modern MySQL uses caching_sha2_password; its full authentication requires a secure connection or an appropriate RSA password exchange. Do not switch to an older plugin just to bypass a connection error. MySQL authentication documentation
Should the report read from a replica?
A read replica can move reporting work away from the primary database, but it may lag behind it. That matters if the report is used to chase orders that were dispatched only minutes ago. Ask the owner how to check replication delay and define how fresh the report must be. MySQL replication overview
Connecting to a replica does not automatically establish read-only permissions or a correct business definition. Review both the identity’s access and the timestamps used in the calculation.
A second workflow: monthly sales with line items
An analyst may need to move from an operational exception list to a sales trend. For a separate schema, ask: “Show completed order value by month for April through June 2026, before refunds, in EUR.” Here each item belongs to one order and unit_price_eur has the agreed price definition.
SELECT DATE_FORMAT(o.ordered_at, '%Y-%m') AS month,
SUM(i.quantity * i.unit_price_eur) AS completed_order_value_eur
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'completed'
AND o.ordered_at >= '2026-04-01'
AND o.ordered_at < '2026-07-01'
GROUP BY DATE_FORMAT(o.ordered_at, '%Y-%m')
ORDER BY month;
DATE_FORMAT supplies the monthly label; the half-open range includes April 1 and excludes July 1. The query sums item values once at item grain. It does not subtract refunds or fill missing months. If the table contains an order-level total repeated on every item, summing that repeated value would inflate the result.
Review one month’s source records before adding a line chart. Business users can confirm which status means a completed sale; analysts can check the join, amount and boundary with the steps in verify AI-generated SQL and results. SQL from the existing report is a useful comparison when its definition matches.
MySQL settings that affect a report
| Setting or type | What to review |
|---|---|
ONLY_FULL_GROUP_BY | Every selected value must have a valid relationship to the grouping; do not disable the mode just to accept an unexplained query |
TIMESTAMP | Session time-zone conversion can affect the period boundary |
DATETIME | The value has no automatic time-zone conversion; establish the convention used by the application |
| Named time zones | Confirm the server has the time-zone information required by the conversion |
| Table-name case and collation | Match discovered names and check comparison rules when moving between environments |
MySQL documents the permitted grouping rules, including functional dependence. With the mode disabled, an ungrouped value can be chosen nondeterministically; adding a customer name to a regional total does not make it a customer-level report. MySQL GROUP BY rules
For time-based reports, confirm column type and session convention instead of treating every timestamp as UTC. Named-zone conversion also depends on server configuration. MySQL time-zone support
Give the database owner a specific access request
Name the database, tables or reporting views, expected reporting period and refresh frequency. If you already have authorised connection details, you can use them directly. If access must be provisioned, the owner should choose an account and host restriction compatible with the service’s network route.
MySQL accounts are identified by user and host. A broad host pattern is not a substitute for deciding which clients may connect. After provisioning, review effective grants in the administrator’s normal tooling; CREATE USER and GRANT do not require a subsequent FLUSH PRIVILEGES. The connection reference keeps these setup details together.
Review relationships and freshness before saving
Where foreign keys are missing, an inferred relationship needs review. Matching column names do not prove that one row has one match. Record the confirmed keys and grain as schema context and business definitions so later questions reuse them. This matters both for counting delayed orders and for summing sales: several item or event rows may belong to one order.
Record whether the connection points to a primary or reporting replica. Choose the acceptable freshness for the decision, and have the owner explain how lag is monitored. A weekly trend and a list used to contact customers about today’s shipments can tolerate different delays.
When a report disagrees with the application, compare timestamps, filters and endpoint freshness before treating the query as wrong. Keep database administration and writes in the tools authorised for that work; Datarelix’s output is a reporting result, not an order-status update.
From rows to a recurring operations view
Start with the table so you can review individual cases. Then ask for delayed-order counts by promised dispatch date or warehouse. Make sure the grouping counts orders rather than order lines. Save a chart to a private dashboard after the result is checked.
A manual refresh reruns the saved query. The fixed cutoff above stays fixed; use an agreed relative cutoff for a daily report. Datarelix reports on the source but does not update order statuses or dispatch shipments.
Follow the delayed-order walkthrough to compare periods and calculate a rate with the right denominator, or start with the quickstart if you have not connected MySQL yet.