A slow query, explained with EXPLAIN.
Analyzing filters and sorting in a Doctrine query against MySQL.
Context
A latest-publications query with visibility filters, roles and associated relationships.
Problem
EXPLAIN ANALYZE showed approximately 252 ms to return three records. The plan included filtering, joins and sorting by created_at and id.
Intervention
Reviewed role filters, used JSON_CONTAINS and added indexes. Inspected the execution plan to locate sorting and selection costs.
Analyze each filter’s selectivity and whether the index also supports the required ordering. A query returning few rows may examine thousands before sorting; adding an index does not guarantee optimizer use or remove join cost.
Observed outcome
Documented the execution plan and remaining sorting as an investigation target. A comparable final measurement is not available to publish a percentage improvement.
Limits and next step
Next: compare plans and latency under the same data and workload. Performance depends on data distribution and access patterns.
A comparison protocol should preserve the dataset, parameters, cache state and concurrency. Beyond total time, inspect examined rows, access methods, sorting and the write cost of new indexes.