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)