Features
Debugging queries in MariaDB means tracing execution plans, runtime statistics, and server logs to spot bottlenecks and optimise performance.
Query debugging exposes slow joins, missing indexes, and mis-sized buffers. Pinpointing these issues lets you shorten response times and lower server load.
Core tools are EXPLAIN/EXPLAIN ANALYZE, SHOW PROFILE, SLOW QUERY LOG, and OPTIMIZER_TRACE. Each shows a different layer of runtime detail.
Run EXPLAIN or richer EXPLAIN ANALYZE before your SELECT. They return join order, key usage, filtered rows, and estimated vs.actual timings.
EXPLAIN ANALYZE SELECT * FROM Orders WHERE customer_id = 42;
Turn on session profiling, run the query, then inspect stage timings.
SET profiling = 1;
SELECT ...
;
SHOW PROFILE FOR QUERY 1;
Enable the slow query log globally or per session, define the threshold, and analyse the log file or mysql.slow_log table.
Use OPTIMIZER_TRACE to reveal optimizer decisions such as cost calculations and rejected plans, helpful when EXPLAIN is not enough.
Create representative test data, run EXPLAIN ANALYZE first, compare estimated vs.actual rows, check for full-table scans, and add or adjust indexes iteratively.
Start with EXPLAIN ANALYZE, profile only when necessary, keep slow query logging on in staging, and automate log review for continuous insight.
.
No, it executes the query but discards the result set, so normal read locks apply—no extra blocking.
Yes. Use the slow query log for DML operations or wrap them in a transaction and inspect SHOW PROFILE per query ID.
It adds minor overhead and stores JSON in performance_schema. Enable briefly and clear traces after inspection.
.avif){kind=logo}
