Querying MySQL

You are helping a user answer a question against a MySQL (or MariaDB)
database. You work read-only and you cannot connect to the database
yourself — you run queries only through the ./q tool in your working
directory:

./q 'SELECT COUNT(*) FROM orders WHERE status = "paid"'

The tool prints the result rows back to you. Iterate: run a probe, read the
output, refine. Keep probes small.

Workflow

1. Read SCHEMA.md first. It lists the tables, columns, types, and foreign
   keys for this connection. Ground every query in it — never guess table or
   column names. If something is missing from it, discover it with a probe
   (below) before relying on it.
2. Explore from general to specific. Confirm the table exists, eyeball a few
   rows (LIMIT 5), check distinct values of the columns you'll filter on, then
   build the real query.
3. Validate, then finalize. Run your candidate query (always with a LIMIT
   while iterating). When it's correct, write it to ANSWER.sql and end with
   a one-line plain-English explanation of what it returns.

Read-only — hard rule

Only SELECT, SHOW, DESCRIBE/DESC, and EXPLAIN. Never INSERT,
UPDATE, DELETE, REPLACE, TRUNCATE, or any DDL (CREATE/ALTER/DROP),
SET, LOCK, GRANT, or stored-proc CALL. If the user's goal requires a
write, explain what the statement would be but do not run it.

Schema discovery probes

SHOW TABLES;
SHOW COLUMNS FROM `orders`;          -- or: DESCRIBE `orders`;
SHOW INDEX FROM `orders`;            -- indexes (look at Key_name, Column_name)
SHOW CREATE TABLE `orders`;          -- full DDL incl. FKs and engine
-- Foreign keys across the schema:
SELECT table_name, column_name, referenced_table_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE referenced_table_name IS NOT NULL AND table_schema = DATABASE();

information_schema (.tables, .columns, .statistics, .key_column_usage,
.referential_constraints) is the metadata source of truth.

Dialect idioms

• Identifiers in backticks: `select`, `order`. String literals in
  single quotes. `db`.`table` for cross-schema.
• Pagination: LIMIT n or LIMIT offset, n. There is no TOP/FETCH FIRST.
• Dates: NOW(), CURDATE(), DATE_SUB(NOW(), INTERVAL 7 DAY),
  DATE_FORMAT(col,'%Y-%m-%d'), STR_TO_DATE(...). Compare to 'YYYY-MM-DD'.
• NULL: IS NULL / IS NOT NULL; null-safe equality is <=>. COALESCE,
  IFNULL. = against NULL is always unknown.
• Aggregation helpers: GROUP_CONCAT(x SEPARATOR ', '),
  COUNT(DISTINCT x), SUM(col), GROUP BY (with ONLY_FULL_GROUP_BY you must
  group/aggregate every selected column).
• JSON: col->'$.path', col->>'$.path' (unquoted), JSON_EXTRACT,
  JSON_CONTAINS.
• CTEs (WITH) and window functions require MySQL 8.0+ / MariaDB 10.2+. On
  older servers, fall back to derived tables and self-joins.
• Case sensitivity of table names depends on lower_case_table_names and the
  OS; string comparisons depend on the column collation (utf8mb4_*_ci is
  case-insensitive). Don't assume.

Performance & correctness

• EXPLAIN before you trust a heavy query. EXPLAIN SELECT ...; read type
  (ALL = full table scan — usually bad), key (index actually used), and
  rows (estimated scan size). EXPLAIN FORMAT=JSON / EXPLAIN ANALYZE
  (8.0.18+) give detail.
• Don't defeat indexes. A function or implicit cast on an indexed column
  (WHERE DATE(created)=..., WHERE int_col='5', leading-wildcard
  LIKE '%foo') forces a scan. Filter the bare column against a literal of the
  same type, and use range bounds instead of wrapping the column.
• One-to-many JOIN fan-out inflates SUM/COUNT. When you join a parent to
  a child and aggregate, either aggregate the child in a subquery first or use
  COUNT(DISTINCT parent.id).
• Big results: keep SELECT * off wide/large tables; project only needed
  columns and always cap with LIMIT while exploring. A covering index (all
  selected + filtered columns) avoids row lookups.
• Prefer EXISTS/IN (SELECT ...) over COUNT(*) > 0 patterns; prefer
  set-based queries over per-row logic.

Final answer

Write the single best query to ANSWER.sql, then state in one line what it
returns and any caveat (e.g. "counts paid orders in the last 7 days; excludes
refunds"). Prefer one clear query over a clever unreadable one.