Why MariaDB Query Execution Time Increases in Enscale
A query that used to run in 50 milliseconds now takes 300. You didn't rewrite it. The schema looks the same. But it's slower, reliably, and it's getting worse. This is one of the most common signs that a database has drifted out of alignment with how it's being used.
The good news is that MariaDB will show you exactly what changed if you ask it the right way.
The tool for that is EXPLAIN. Here's how to read it, what makes execution time creep up, and why chasing one query at a time rarely makes it stop.
Key Takeaways
- The same query can get slower with no code change, because its conditions shift: data volume, index selectivity, memory, and concurrency.
- Use
EXPLAINto see the plan.type: ALLwithkey: NULLmeans a full table scan with no index.- Most cases come down to a missing or no-longer-selective index, which you can usually fix without a schema change.
- Rising execution time across many queries at once signals broader misalignment, not a one-query problem.
Execution time is about the whole system, not just the query
It's easy to assume a slow query is a query problem. Usually it's a system problem wearing a query's clothes. Execution time reflects data volume and distribution, index effectiveness, memory allocation, concurrency, and the plan the optimizer chooses. Change any of those and the same SQL costs more to run, even though the text on the screen is identical.
That's why "the query didn't change" is a red herring. The query is fixed; its environment isn't.
Use EXPLAIN to see what actually changed
EXPLAIN shows you the plan MariaDB intends to use. Put it in front of the query:
EXPLAIN SELECT * FROM orders WHERE customer_id = 40219 ORDER BY created_at DESC;A few columns carry most of the meaning:
- type is the access method.
reforrangemeans an index is being used.ALLmeans a full table scan, reading every row. SeeingALLon a large table is the classic cause of a query that's gotten slow. - key is the index actually chosen.
NULLhere means no index is being used at all. - rows is roughly how many rows MariaDB expects to examine. Watch this number grow over time for the same query, and you're watching the cost of that query rise in real time.
- Extra carries warnings like
Using filesortorUsing temporary, both of which point to expensive work happening in the background.
The revealing move is to run EXPLAIN on the same query now that you'd have run months ago. A query that used to report type: ref with a named key and a few hundred rows, but now reports type: ALL, key: NULL, and hundreds of thousands of rows, has quietly stopped using its index. Same SQL, completely different plan, and there's your 50ms-to-300ms right there.
For the real numbers rather than the estimate, use MariaDB's ANALYZE statement: ANALYZE SELECT ... runs the query and annotates the plan with actual row counts (the r_rows column) beside the estimates, and ANALYZE FORMAT=JSON SELECT ... adds per-step timings, so you can see where the time genuinely goes. (MariaDB uses ANALYZE as the leading keyword; EXPLAIN ANALYZE is PostgreSQL and MySQL syntax and won't run here.)
Why the plan degrades over time
Queries don't slow down on their own. The conditions around them shift:
- Data grows. A full scan that was trivial at 10,000 rows is punishing at 10 million. Response times that were fine turn into multi-second waits once the table is large enough.
- Indexes lose selectivity. As the data distribution changes, an index that used to narrow results sharply may no longer do so, and the optimizer may abandon it.
- A schema change invalidated an index. Sometimes an index quietly stopped covering the query it was built for.
- Memory pressure rises. When the working set outgrows the buffer pool, more of the query's work happens against disk instead of memory.
- Concurrency and lock contention increase, so the query spends time waiting rather than executing.
Most of the time, the root cause is a missing or no-longer-selective index, and it can be fixed without touching the schema at all. But you have to see it first, which is what EXPLAIN is for.
Why fixing one query isn't enough
You can optimise the slow query in front of you and it'll get faster. If the underlying system is still misaligned, though, another query degrades next, then another, and you're back in the same place a few weeks later. Individual fixes treat the symptom. Rising execution time across many queries is telling you something broader: the configuration, indexing, and schema have collectively fallen behind the workload. That's the thing worth addressing, and it's a moving target, because the workload keeps evolving.
Where Releem fits
Running EXPLAIN on a query you already suspect is one thing. Continuously watching execution patterns across your whole workload, catching the query that's about to become a problem before it shows up in a support ticket, is another, and it's the part that doesn't scale by hand.
Releem runs as an add-on inside your Enscale environment and analyses execution patterns alongside configuration and schema, so it can point out where misalignment is starting and what to do about it, before it compounds. It's trained machine learning rather than an LLM, so its recommendations come from real patterns in your database instead of predicted text, with no hallucinated answers. Applying a change is your call: Releem recommends the fix and tells you why, and you apply it yourself, in a click in the dashboard or with your own database tools, once you've read it. Manual review is the default and how we'd recommend running it, and if you later decide you want Releem applying routine changes for you, that's your decision to make. And the longer it runs, the more its advice is tuned to your environment and the fixes that have already worked for you.
Frequently asked questions
Why does the same MySQL or MariaDB query get slower over time?
Because its conditions change even though the SQL doesn't. Tables grow, indexes lose selectivity or get invalidated, the working set outgrows the buffer pool so reads hit disk, and concurrency rises. Any of these raises the cost of running the exact same query.
How do I use EXPLAIN to find out why a query is slow?
Run EXPLAIN before the query and read the type, key, and rows columns. type: ALL with key: NULL means a full table scan with no index. A high or growing rows estimate shows the query examining more data than it should. MariaDB's ANALYZE SELECT goes further, executing the query and reporting the actual row counts (r_rows) beside the estimates, and ANALYZE FORMAT=JSON adds the real per-step timings.
My query plan changed but I didn't change the query. Why?
The optimizer chooses a plan based on current data statistics. As data grows or its distribution shifts, an index that used to look attractive can stop being selective, so MariaDB switches to a different plan, sometimes a full scan. The query text is the same; the data underneath it moved.
Will adding an index fix increasing execution time?
Often, yes, since most cases come down to a missing or no-longer-selective index, and that can usually be fixed without a schema change. But if execution time is rising across many queries at once, the real issue is broader misalignment between your configuration, indexing, and workload, and that needs ongoing attention rather than a single index.
What's the difference between EXPLAIN and ANALYZE in MariaDB?
EXPLAIN shows the plan MariaDB intends to use and its estimates without running the query. ANALYZE (written ANALYZE SELECT ..., MariaDB's equivalent of the EXPLAIN ANALYZE you may know from PostgreSQL or MySQL) actually executes the query and annotates the plan with the real row counts, and with FORMAT=JSON the real per-step timings, which is more accurate for diagnosing where the time is genuinely being spent.
If your query times are creeping up across the board, drift is already underway and single fixes won't hold it back. Releem watches execution patterns continuously and tells you what to change, while you keep the final say.
See how Releem works on Enscale, and install it on your database node →
Already an Enscale customer and ready to go? Install Releem now →
