Investigate a slow MySQL query with EXPLAIN

Record the query and measurement context first

Investigating a slow MySQL query does not start by adding an index. Record the exact SELECT, parameter values, server version, existing indexes, data volume and the circumstances in which the delay occurs. Separate lock waits from execution work and I/O. The example below uses eight fictional orders and the same query before and after a candidate index. It is reproducible teaching material, not a production benchmark or a measured speed improvement. Consult documentation for your actual server version when its output differs, and retain the original plan for comparison.

Run the demonstration only in a test environment

Open a fresh connection to a separate MySQL 8.4 test database. Run the complete block in that session and stop at the first error. It creates only a session-scoped temporary table. Do not run it in production or reuse an existing session with a same-named table. Close the connection to remove the temporary table. EXPLAIN FORMAT=TRADITIONAL makes the intended output format explicit; the resulting MySQL plan must not be confused with a SQLite or SQL Server plan. Creating the table requires appropriate temporary-table privileges.

-- Synthetic MySQL 8.4 demo. Use a NEW session in a separate test database.
-- Never run in production. Stop at the first error; close the session afterwards.
CREATE TEMPORARY TABLE cg_explain_orders (
    id INTEGER NOT NULL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    created_at DATETIME NOT NULL
);
INSERT INTO cg_explain_orders VALUES
    (1,42,'2026-08-31 23:59:59'), (2,42,'2026-09-01 00:00:00'),
    (3,42,'2026-09-01 12:00:00'), (4,42,'2026-09-01 12:00:00'),
    (5,42,'2026-09-02 00:00:00'), (6,7,'2026-09-01 12:00:00'),
    (7,42,'2026-09-01 23:59:59'), (8,42,'2026-09-02 00:00:01');

EXPLAIN FORMAT=TRADITIONAL
SELECT id, customer_id, created_at FROM cg_explain_orders
WHERE customer_id = 42
  AND created_at >= '2026-09-01 00:00:00'
  AND created_at < '2026-09-02 00:00:00'
ORDER BY created_at DESC, id DESC;

SELECT id, customer_id, created_at FROM cg_explain_orders
WHERE customer_id = 42
  AND created_at >= '2026-09-01 00:00:00'
  AND created_at < '2026-09-02 00:00:00'
ORDER BY created_at DESC, id DESC;

CREATE INDEX cg_explain_customer_date_id
ON cg_explain_orders(customer_id, created_at, id);

EXPLAIN FORMAT=TRADITIONAL
SELECT id, customer_id, created_at FROM cg_explain_orders
WHERE customer_id = 42
  AND created_at >= '2026-09-01 00:00:00'
  AND created_at < '2026-09-02 00:00:00'
ORDER BY created_at DESC, id DESC;

SELECT id, customer_id, created_at FROM cg_explain_orders
WHERE customer_id = 42
  AND created_at >= '2026-09-01 00:00:00'
  AND created_at < '2026-09-02 00:00:00'
ORDER BY created_at DESC, id DESC;
-- Both SELECTs should return IDs 7, 4, 3, 2. No timing claim follows from this.

Do not read estimates as measurements

In traditional EXPLAIN, possible_keys lists candidate indexes and key identifies the chosen index. type describes the access method, while rows and filtered help interpret estimated work. Extra adds information such as sorting. Seeing an index name does not prove that the complete query is efficient. For eight rows, a table scan may cost less, so not selecting the candidate index is not automatically a defect. Focus on the hypothesis: could the combination of customer, date range and ordering reduce the amount of work for a representative dataset?

Check correctness before speed

Both SELECTs should return IDs 7, 4, 3 and 2. Record 2 checks the included start boundary; records 5 and 8 fall outside the half-open interval. Record 6 belongs to another customer. Equal timestamps for records 3 and 4 are ordered deterministically by the additional ID sort. For a real workload, also cover nullable fields, empty results, large customers and representative data distribution. Equal row counts alone do not prove equal content. Compare returned identifiers, values and ordering, and preserve tenant restrictions or other authorization filters throughout any later rewrite.

Measure the real workload under control

EXPLAIN ANALYZE actually executes a supported SELECT and provides runtime information. That is useful, but it is not a harmless way to inspect an expensive production query. Use a representative test environment and record version, parameters, cache conditions and concurrent load. Repeat measurements instead of reporting only the fastest run. Measure write costs and index storage as well. The fixture described here checks logic, not production performance, and cannot justify a general speed claim. Record what remains untested before proposing an index for deployment or declaring the incident resolved.

Continue the investigation

A query plan is one part of the diagnosis. The following references connect the technical investigation to a scoped SQL service. Demonstrations use fictional data, not the results of a client engagement. For an enquiry, start with a problem description and anonymised context. Do not send passwords, complete database dumps or customer personal data.

• Choose database indexes from real Laravel queries
• MySQL migration: check performance and data consistency
• Discuss SQL query optimisation
• Reproducible SQL demonstration
• Request a SQL analysis