咨询：针对1000条患者数据的SQL查询优化方案

Hey there, let's break down how to optimize this query step by step—10 seconds on a t2.micro is way too slow for only 1000 patient rows, so let's get that runtime way down.

Your current query uses three correlated subqueries in the SELECT clause. That means for every single patient row (1000 total), the database runs three separate queries against the invoice and payment tables. That's 3000 extra queries right there—no wonder it's slow.

Instead, we can pre-aggregate the invoice and payment data once for all patients, then join those aggregated results back to the patient table. This cuts down the number of aggregate operations from 3000 to just 2 (one for invoices, one for payments).

Without proper indexes, the database is doing full table scans on the invoice and payment tables every time it runs those subqueries. Let's add indexes that cover exactly what we need:

• For the invoice table:

  CREATE INDEX idx_invoice_patient_invoiced ON invoice (patientid, invoicednumber) INCLUDE (amount, discount);
  

  This index lets the database quickly find all valid invoices for a patient, and it includes the amount and discount columns so it doesn't have to go back to the main table to fetch them (this is called a covering index).

• For the payment table:

  CREATE INDEX idx_payment_patient ON payment (patientid) INCLUDE (amount, paymentdate);
  

  Similarly, this index lets the database quickly find all payments for a patient, and includes the columns we need for sum and max calculations without extra table lookups.

(Note: If you're using MySQL, the INCLUDE syntax isn't supported—instead, add those columns to the index directly: CREATE INDEX idx_payment_patient ON payment (patientid, amount, paymentdate);)

Here's the optimized query that uses pre-aggregated joins instead of correlated subqueries:

SELECT
  p.patientid,
  p.firstname,
  p.lastname,
  p.mobilephone,
  p.email,
  -- Calculate the balance using pre-aggregated values
  FORMAT(
    COALESCE(
      (inv.total_due - pay.total_paid),
      0
    ),
    0
  ) AS answer,
  -- Get last payment date from pre-aggregated payments
  DATE_FORMAT(pay.last_payment_date, '%d-%m-%Y') AS lastpaymentdate
FROM patient p
-- Left join to get aggregated invoice data for each patient
LEFT JOIN (
  SELECT
    patientid,
    SUM(amount) - SUM(amount * (discount / 100)) AS total_due
  FROM invoice
  WHERE invoicednumber > 0
  GROUP BY patientid
) inv ON inv.patientid = p.patientid
-- Left join to get aggregated payment data for each patient
LEFT JOIN (
  SELECT
    patientid,
    SUM(amount) AS total_paid,
    MAX(paymentdate) AS last_payment_date
  FROM payment
  GROUP BY patientid
) pay ON pay.patientid = p.patientid
WHERE 1;

Why this works better: #

• The two subqueries in the LEFT JOIN run once each, aggregating all invoice and payment data by patient ID upfront.
• We avoid the row-by-row subquery execution that was killing performance.
• The joins are efficient because we're using the indexes we created earlier.

While the SQL and index fixes will give you the biggest speedup, the t2.micro has limited CPU and memory (only 1GB RAM). If your database is running on the same instance, you might want to:

• Temporarily upgrade to a t2.small (2GB RAM) to see if memory constraints were causing extra disk I/O (since the database can't cache as much data).
• Make sure your database's buffer pool size (e.g., innodb_buffer_pool_size for MySQL) is set to a reasonable value—aim for 50-70% of available RAM.

After implementing these changes, you should see the runtime drop from 10 seconds to well under a second even on a t2.micro.

内容的提问来源于stack exchange，提问作者Jean