MySQL Query Optimization for Reporting Queries

How to speed up slow MySQL reporting queries: reading EXPLAIN, composite and covering indexes, sargable conditions and summary tables.

Transaction screens usually stay fast because they touch a few rows by primary key. Reports are different: they filter by date, group by product or territory and read thousands of rows. When a report that took two seconds last year takes forty today, these are the steps I follow.

Start with EXPLAIN

Put EXPLAIN in front of the query and read three columns. type shows how the table is accessed: ALL means a full table scan. key shows the index used, if any. rows is the estimated number of rows examined. A report that examines two million rows to return fifty is missing an index.

EXPLAIN
SELECT product_id, SUM(quantity) AS qty, SUM(amount) AS total
FROM order_lines
WHERE order_date BETWEEN '2026-08-01' AND '2026-08-31'
  AND territory_id = 12
GROUP BY product_id;

Build composite indexes in the right order

MySQL uses a composite index from its leftmost column. Put columns compared with equals first and the range column after them. For the query above, an index on (territory_id, order_date) lets MySQL jump to one territory and read only August within it. The reverse order works less well, because after a range condition the rest of the index cannot narrow the search.

CREATE INDEX idx_lines_territory_date
    ON order_lines (territory_id, order_date);

Use a covering index for hot reports

If an index contains every column the query needs, MySQL answers from the index alone and never reads the table rows. EXPLAIN shows this as Using index. Adding product_id, quantity, amount to the index above makes it covering for this report. It costs disk space and slower writes, so reserve it for the queries that run most.

Keep conditions sargable

A function wrapped around a column stops MySQL using an index on it. Rewrite the condition so the column stands alone.

-- cannot use an index on order_date
WHERE YEAR(order_date) = 2026 AND MONTH(order_date) = 8

-- can use it
WHERE order_date >= '2026-08-01' AND order_date < '2026-09-01'

The same applies to a leading wildcard such as LIKE '%phone' and to comparing a string column with a number, which forces a conversion on every row.

Other habits that help

• Select only the columns you need instead of SELECT *.
• Filter before joining: reduce the big table first, then join the small ones.
• Turn on the slow query log to find which queries deserve attention.
• Review indexes occasionally; unused ones still slow every insert.

When indexing is not enough

If a report sums a year of order lines every time it opens, no index makes that cheap. Store daily totals in a summary table updated by a scheduled job, and let the report read those. When several reports need this, it is time to consider a data warehouse. On the application side, the matching checklist is Laravel performance tuning.