# MariaDB Slow Queries on 11/12: Diagnosis Steps Source: https://srvscripts.com/guides/mariadb-slow-queries-11-12/ Updated: 2026-10-03 Publisher: srvScripts (https://srvscripts.com/) On a shared server, “the database is slow” almost always means “three queries from two customers are slow and everyone else is waiting behind them”. The tooling to prove which queries, and why, is built into MariaDB and costs nothing to enable. This guide follows the path we take: capture with the slow log, rank, explain, and only then reach for the optimizer trace or a hint. It applies to 10.11 onward, with the hint syntax specific to 12.x. In short: Enable the slow log live with SET GLOBAL slow_query_log = 1 and long_query_time = 1, let it collect for an hour, then rank with mariadb-dumpslow -s t to find the queries costing the most total time. **Short answer:** Enable the slow log live with `SET GLOBAL slow_query_log = 1` and `long_query_time = 1`, let it collect for an hour, then rank with `mariadb-dumpslow -s t` to find the queries costing the most total time. Run the worst through `EXPLAIN` and `ANALYZE` to spot full scans, filesorts and estimates that do not match real row counts, fix with an index or `ANALYZE TABLE`, and reserve the optimizer trace and the 12.x `/*+ INDEX() */` hints for queries you cannot change. ## Turn on the slow log without a restart ``` mariadb -e "SET GLOBAL slow_query_log = 1; SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log'; SET GLOBAL long_query_time = 1; SET GLOBAL log_queries_not_using_indexes = 0; SET GLOBAL log_slow_verbosity = 'query_plan,explain';" ``` One second is a reasonable threshold on a shared server; anything slower than that is worth a look and the log stays readable. `log_slow_verbosity = 'query_plan,explain'` is a MariaDB extension that writes the execution plan alongside each slow query, which saves a round trip later. Leave `log_queries_not_using_indexes` off: on a WordPress server it fills the log with harmless tiny scans. Make it persistent in `/etc/my.cnf.d/slowlog.cnf` so it survives restarts, and rotate the file with logrotate; a forgotten slow log on a busy server grows to gigabytes. ## Rank what you captured After an hour, or a day for an intermittent problem, aggregate: ``` mariadb-dumpslow -s t -t 15 /var/lib/mysql/slow.log ``` `-s t` sorts by total time, which surfaces the queries that hurt most in aggregate, including fast queries that run thousands of times. `-s at` sorts by average time and finds the individually slow ones. The tool normalises literals so identical query shapes group together. Note the schema name in each entry: on a cPanel or DirectAdmin server it tells you which customer, and the [MySQL health snapshot script](/scripts/mysql-health-snapshot/) correlates that with per-schema size and connection counts. If `pt-query-digest` from Percona Toolkit is installed it gives a richer report, but `mariadb-dumpslow` is always present. ## Explain the top offenders Take the worst query, substitute real values from the log, and run it through `EXPLAIN`: ``` EXPLAIN SELECT ... ; EXPLAIN FORMAT=JSON SELECT ... ; ANALYZE SELECT ... ; ``` `EXPLAIN` shows the plan the optimizer intends. `ANALYZE` (a MariaDB addition) runs the query and shows the actual row counts next to the estimates, which is how you spot an optimizer that guessed wrong. The things to look for, in order: a `type` of `ALL` on a large table (full scan, missing index), `Using filesort` or `Using temporary` on a big result (sort without an index), and `rows` estimates wildly different from the `r_rows` that `ANALYZE` reports (stale statistics). Stale statistics are common on hosting servers because tables are created and bulk-loaded by installers and never analysed: ``` ANALYZE TABLE shop_db.wp_postmeta; ``` If the plan changes after that, the fix was free. ## The WordPress special cases Two query shapes account for most slow-log entries on shared servers. The first is `wp_options` scans caused by the `autoload` column having no useful index on old installs; the second is `wp_postmeta` joins filtering on `meta_value`, which is a `LONGTEXT` and cannot be indexed conventionally. For the first, an index on `(autoload)` is added by recent WordPress versions and can be added by hand. For the second, the honest answer is that the customer’s plugin is doing something the schema cannot support; a prefix index on `meta_value(191)` helps some cases and is worth suggesting. ## Optimizer trace for the hard cases When `EXPLAIN` shows a plan that seems wrong and `ANALYZE TABLE` did not fix it, the trace shows why the optimizer chose it: ``` SET optimizer_trace = 'enabled=on'; SELECT ... ; SELECT * FROM information_schema.OPTIMIZER_TRACE\G SET optimizer_trace = 'enabled=off'; ``` The output is long JSON. Search it for `considered_execution_plans` and `rows_estimation` to see each candidate plan with its estimated cost, and for `rejected` to see which indexes were dismissed and why. The common finding is that the optimizer thought a range on an index would return most of the table and chose a scan instead; that usually traces back to histogram statistics being absent, which `ANALYZE TABLE ... PERSISTENT FOR ALL` fixes on 10.4 and later. ## Optimizer hints in 12.x MariaDB 12.x added hints in the `/*+ ... */` comment style, which let you force or forbid a specific choice without touching the schema or global settings. They are the right tool when a customer’s application generates a query you cannot change but the optimizer picks the wrong index: ``` SELECT /*+ INDEX(p idx_post_date) */ ... FROM wp_posts p WHERE ... ; SELECT /*+ NO_INDEX(m idx_meta_value) */ ... FROM wp_postmeta m ... ; SELECT /*+ JOIN_ORDER(p, m) */ ... ; ``` On earlier versions the older `USE INDEX` and `FORCE INDEX` syntax in the `FROM` clause does much the same for index choice, without the join-order control. Hints are a targeted fix, not a policy; document each one and revisit it after the next upgrade, since the optimizer improves between releases and a hint can become the thing that is holding a query back. **Common pitfall.** Raising `long_query_time` to 10 so the log stays quiet, then concluding there are no slow queries. On a shared server the damage is done by queries that take half a second and run constantly. Capture at one second, sort by total time, and let the aggregate tell the story. ## Verify After an index, an `ANALYZE TABLE` or a hint, prove the improvement rather than assuming it: ``` ANALYZE SELECT ... \G ``` The `r_total_time_ms` in the output should be a fraction of what it was, and `r_rows` on the relevant table should match the estimate. Then watch the slow log for the next hour and confirm that query shape has dropped out of the `mariadb-dumpslow` top list. Turn the verbosity back down once the investigation is over, keep the slow log enabled at one second permanently, and check it weekly; the next slow query is already being written by a plugin update somewhere on the server. ## MariaDB slow queries at a glance **Official documentation:** [MariaDB documentation](https://mariadb.com/docs/), [Linux man pages](https://man7.org/linux/man-pages/). **Related guides:** [Fixing “unknown variable” startup failures after a MariaDB upgrade](https://srvscripts.com/guides/mariadb-unknown-variable-after-upgrade/) · [Tuning InnoDB on MariaDB 11/12 for cPanel shared hosting: buffer pool, redo log and I/O](https://srvscripts.com/guides/innodb-tuning-mariadb-shared-hosting/) · [Fix “Too many connections” on MariaDB/MySQL (cPanel)](https://srvscripts.com/guides/fix-mysql-too-many-connections-cpanel/). ## Frequently asked questions ### Does enabling the slow query log slow down MariaDB? Negligibly at a one-second threshold, because only queries over the limit are written; the real risk is disk growth, so give the log a logrotate entry and leave log_queries_not_using_indexes off. ### How long should the slow log run before analysing it? An hour is enough for a persistent problem; for an intermittent one capture a full day so the aggregate in mariadb-dumpslow reflects the busy periods rather than the moment you happened to look. ### Can I remove an optimizer hint after upgrading MariaDB? Yes, and each one should be reviewed after every upgrade: re-run ANALYZE SELECT without the hint, and if the optimizer now picks the right plan on its own, drop the hint so it cannot hold the query back later.