Why MariaDB query latency fell from 18.43 to 0.70 ms when the name already matched one row
With 3,759 distinct names across 3,791 rows, the name column in ts_variable already did almost all the filtering. For mysql_available, it matched exactly one row. Adding v.`from` = 'mysql_server' kept that same row. A negligible speedup seemed like a reasonable prediction. Yet at roughly 2,100 calls per minute, average production latency fell from 18.43 to 0.70 ms. What did MariaDB gain when the second predicate removed zero additional rows for this metric?
The source distinguishes just 32 more groups across 3,791 rows
We grouped the 3,791 rows in ts_variable in a single read-only snapshot on 21 November 2026 at 21:25 CET. The percentage below is the number of distinct groups divided by the table’s row count.
| Grouped columns |
Exact groups |
Groups / rows |
SHOW INDEX before → after |
(name) |
3,759 |
99.16% |
3,841 → 3,840 |
(name, from) |
3,791 |
100% |
3,841 → 3,840 |
(from) |
42 |
1.11% |
42 → 42 |
The first two rows are successive prefixes of the unique index named name. The third uses the separate index on from. At the initial reading, MariaDB reported 3,841 at both positions of the unique index. This statistic is an estimate and can exceed the actual row count. MariaDB cardinality reference.
The counts explain that initial expectation. 3,729 names occur once, 28 twice and 2 three times. Grouping by (name, from) adds only 32 groups across the entire table. The ratio of distinct groups to rows rises from 99.16% to 100%, just 0.84 percentage points. For the availability metric, even that small difference disappears. The name mysql_available matches one row, and specifying the source matches the same row.
SELECT rows_per_name, COUNT(*) AS number_of_names
FROM (
SELECT name, COUNT(*) AS rows_per_name
FROM ts_variable
GROUP BY name
) AS names
GROUP BY rows_per_name
ORDER BY rows_per_name;
After refreshing statistics for ts_variable
At 21:30 CET, ANALYZE NO_WRITE_TO_BINLOG TABLE pmacontrol.ts_variable returned OK on the monitored instance. SHOW INDEX went from 3,841 to 3,840 for both prefixes. Repeating the group counts immediately afterwards gave the same exact cardinalities.
InnoDB records prefix estimates in mysql.innodb_index_stats. For the name index, n_diff_pfx01 went from 3,757 to 3,759 and n_diff_pfx02 from 3,789 to 3,791. Both now match the exact counts. Before and after the refresh, EXPLAIN estimated one row for each isolated lookup on ts_variable. Access remained ref for the name and const for the pair.
SQL · TSV · TSV (21:30)
In the tables, green (✓) marks an improvement and red (✗) a regression when the larger value is strictly more than twice the smaller one. Durations must also differ by at least 1 ms. Call volumes describe workload. The threshold is a reading aid.
Execution plans side by side
Each cell shows the join position, access type, chosen index and key length in bytes. Rows are aligned by table.
| Table |
Before |
After |
ts_file (f) |
1 · const · file_name · 130 B |
1 · const · file_name · 130 B |
ts_max_date (m) |
2 · const · id_mysql_server_2 · 8 B |
3 · const · id_mysql_server_2 · 8 B |
ts_variable (v) |
3 · ref · name · 102 B |
2 · const · name · 168 B |
mysql_server (s) |
4 · ref · PRIMARY · 4 B |
5 · ref · PRIMARY · 4 B |
ts_value_general_int (t) |
5 · eq_ref · PRIMARY · 13 B |
4 · const · PRIMARY · 13 B |
In both plans, MariaDB estimates one row for each of the five tables and lists the same 32 partitions for ts_value_general_int.
Where the time changed
| Phase · median across 50 servers |
Before |
After |
| Optimization |
35.085 ms |
✓ 0.261 ms (÷134.27) |
| Execution after optimization |
0.107 ms |
0.011 ms |
Statistics stage |
54.983 ms |
✓ 0.224 ms (÷245.46) |
The first two rows come from ANALYZE FORMAT=JSON. The Statistics row comes from a separate SHOW PROFILE run. These timings were collected separately.
With name alone, MariaDB estimates that it will read one row. With both equalities, the unique index on (name, from) guarantees at most one match. MariaDB can therefore treat v.id as a constant while planning the join to the values table. Across 50 servers, median planning time fell from 35.085 to 0.261 ms. Execution after planning fell from 0.107 to 0.011 ms. Planning the joins accounted for most of the measured saving.
Two hours around deployment
| Metric |
Hour before |
Hour after |
| Estimated calls |
126,229 |
123,241 |
| Executions per minute |
2,104 |
2,054 |
| Weighted mean latency |
18.43 ms |
✓ 0.70 ms (÷26.25) |
| Cumulative SQL seconds |
2,326.5 |
✓ 86.5 (÷26.88) |
| Executions per SQL second |
54 |
✓ 1,424 (×26.25) |
| Rows examined per call |
2.99 |
✓ 1.00 (÷2.98) |
The 50-server check
| Variant |
Minimum (ms) |
Median (ms) |
Mean (ms) |
Maximum (ms) |
| Before |
2.159 |
12.543 |
21.686 |
117.530 |
| After |
✓ 0.535 (÷4.03) |
✓ 0.755 (÷16.62) |
✓ 0.804 (÷26.98) |
✓ 2.446 (÷48.05) |
Alternatives compared with the deployed query
Each row compares mean statement times on the same servers in the same experiment. Each series covers 50 servers. The number in parentheses is the execution count per variant.
| Variant |
Reference (ms) |
Variant (ms) |
| Name only (150) |
0.651 |
✗ 36.782 (×56.50) |
| Name only, forced primary key on values (150) |
0.651 |
✗ 1.674 (×2.57) |
| Full key, forced primary key on values (150) |
0.651 |
0.630 |
| Forced covering index on the date table (50) |
0.692 |
✗ 20.232 (×29.25) |
Active server checked with EXISTS (150) |
0.651 |
0.663 |
| Scalar lookup of the value (150) |
0.617 |
0.546 |
| Forced join order (50) |
0.692 |
✗ 41.737 (×60.34) |
Scalar lookup with EXISTS (150) |
0.617 |
0.563 |
Scalar lookup without LIMIT (150) |
0.617 |
0.560 |
Deployed joins without LIMIT (150) |
0.617 |
0.602 |
| Scalar lookup with name only (150) |
0.617 |
1.218 |
Full plan before (CSV) · Full plan after (CSV)