MariaDB Composite Index: Hold the Magnet by the Right End

Both the N+1 article and the million-row WHERE article (in Turkish) ended on the same advice: "look at EXPLAIN." Good advice, just incomplete. EXPLAIN tells you what the optimizer plans to do. It does not tell you what it did.

So this time the conversation about MariaDB composite index order runs on counters, not estimates. I built a million-row orders table, ran one query against five different indexes, and recorded how many rows and pages MariaDB actually touched each time.

The metaphor is an old one. The table is a haystack, the seven rows you want are needles, and an index is a magnet. What usually goes unsaid is that a magnet held by the wrong end drags half the haystack along with the needles. That was the sharpest result of the whole run; a composite index in the wrong column order was three times slower than having no index at all.

MariaDB Has No EXPLAIN ANALYZE. It Has ANALYZE.#

MySQL has shipped EXPLAIN ANALYZE since 8.0.18. Bring that habit to MariaDB and this is your welcome:

EXPLAIN ANALYZE SELECT 1;
-- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
-- that corresponds to your MariaDB server version for the right syntax
-- to use near 'ANALYZE SELECT 1' at line 1

In MariaDB the same job has been done by the ANALYZE statement since 10.1. Sergei Petrunia, who wrote the feature, explains in the comments of his announcement post that they first planned to call it EXPLAIN ANALYZE, as PostgreSQL does, and dropped the name after objections.

According to the official documentation, ANALYZE actually executes the statement, discards the result set and hands you the plan, with two observed columns next to the estimated ones:

• rows / r_rows: how many rows the optimizer expected versus how many were really read.
• filtered / r_filtered: the expected and actual share of rows that survived the WHERE clause.

ANALYZE FORMAT=JSON goes further: r_total_time_ms per node and, for InnoDB tables, r_engine_stats.pages_accessed, the number of buffer pool pages touched. That last counter is available from 10.6.15 and 10.11.5 onwards.

Hard truth: ANALYZE UPDATE and ANALYZE DELETE really do modify data; the documentation says so in plain words. And do not confuse it with ANALYZE TABLE, a different command that runs no query and only collects statistics.

The Haystack: A Million Orders#

I filled the table with MariaDB's Sequence engine, no external script involved. seq_1_to_1000000 behaves like a ready-made numbers table, and CRC32 keeps the columns independent of each other.

CREATE TABLE siparisler (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  musteri_id INT UNSIGNED NOT NULL,
  durum ENUM('beklemede','odendi','iptal','iade') NOT NULL,
  tutar DECIMAL(10,2) NOT NULL,
  adres_notu VARCHAR(255) NOT NULL,
  olusturma DATETIME NOT NULL
) ENGINE=InnoDB;
​
INSERT INTO siparisler (musteri_id, durum, tutar, adres_notu, olusturma)
SELECT
  1 + CRC32(CONCAT('m', seq)) % 20000,
  ELT(1 + CASE WHEN CRC32(CONCAT('d', seq)) % 10 < 7 THEN 1
               WHEN CRC32(CONCAT('d', seq)) % 10 < 8 THEN 0
               WHEN CRC32(CONCAT('d', seq)) % 10 < 9 THEN 2
               ELSE 3 END,
      'beklemede', 'odendi', 'iptal', 'iade'),
  ROUND(50 + (seq * 37) % 4950 + (seq % 100) / 100, 2),
  REPEAT('x', 40 + seq % 120),
  '2024-01-01' + INTERVAL CRC32(CONCAT('t', seq)) % (1000 * 86400) SECOND
FROM seq_1_to_1000000;

The column names are Turkish, straight from the original run: siparisler is orders, musteri_id customer id, durum status (odendi means paid), tutar amount, olusturma created at. The result: 20,000 customers, 70% of orders paid, dates from January 2024 to September 2026, 143.7 MB of data. The needle is the classic query behind a customer's order history screen:

SELECT id, tutar, olusturma
FROM siparisler
WHERE musteri_id = 4242
  AND durum = 'odendi'
  AND olusturma >= '2026-01-01'
ORDER BY olusturma DESC
LIMIT 20;

It returns 7 rows. The same table holds 265,171 orders from 2026. Keep both numbers in mind.

Technical detail: measured on MariaDB 10.11.14 with a 512 MB buffer pool and the table fully cached. The milliseconds belong to this machine; the row and page counts are what travels.

Five Magnets, Five Measurements#

Single-column index: the optimizer was right to walk away#

The index on olusturma showed up in possible_keys and went unused. Orders from 2026 are more than a quarter of the table; hopping from the index to the table that many times costs more than reading the table end to end. Nothing to blame the optimizer for here.

Wrong column order: worse than the haystack#

This is the real lesson. Same three columns, different order, and the query runs three times slower than with no index. Three repeats all landed between 535 and 568 ms.

Diagnosis: the plan says range, but ANALYZE shows what happened inside. MariaDB walked the index backwards in olusturma order (Handler_read_prev: 265,171), visiting every single 2026 order. Pages touched went from 9,130 to 795,861. There is no Using index condition in Extra; musteri_id and durum in the index never narrowed the search.

Why this plan? The ORDER BY olusturma DESC LIMIT 20 is the hint. This index already returns rows in the requested order, so the plan looks built on the assumption that it can skip the sort and stop after 20 rows. Only 7 rows match. A scan that never finds twenty runs to the end of the range. The check is simple: with the same index in place, removing ORDER BY and LIMIT sent the optimizer straight back to a full scan (172 ms, 9,130 pages).

Reality check: an index appearing in the plan does not mean it is helping. The gap between rows and r_rows (493,596 against 265,171) was already saying something was off.

Right order: equality first, range last#

With (musteri_id, durum, olusturma) the estimate is 7, rows read are 7, pages 42. The magnet drops to the customer, then the status; the date range sits last, so the read inside that narrow slice is already ordered and the ORDER BY comes for free.

Rick James's article on compound indexes, part of the MariaDB documentation, shows with examples why column order matters. I checked the equality-first rule with two side measurements:

• With (musteri_id, olusturma, durum) rows read rose to 10. Once the range is in the middle, the column after it cannot narrow the search; all of the customer's 2026 orders are read and durum is filtered afterwards. The gap is small here because a customer has about 50 orders on average. It will not stay small for a busy B2B account.
• With the right index in place, dropping musteri_id from the WHERE clause pushed the plan to type index: all one million index entries were scanned. Without the first column, the magnet has no handle.

Covering index: never touching the table#

The query asks for id, tutar and olusturma. Appending tutar to the index brought Using index into Extra and cut pages from 42 to 3. In InnoDB every secondary index already carries the primary key, so id needed nothing extra.

The bill: index size went from 21.5 MB to 27.6 MB, and every INSERT and UPDATE now writes one more column. Rewriting the same query as SELECT * made Using index vanish instantly. A covering index is a luxury for the one or two most frequent queries behind a page; put one on every query and the write side pays for it.

On the PHP Side: Testing a Query with the Magnet#

Writing ANALYZE by hand and reading the JSON is fun twice. After that, a small framework-agnostic function (the identifiers are Turkish, as in the original: planRaporu is "plan report", tahmin estimate, okunan rows read, sayfa pages, kapsayan covering):

<?php
declare(strict_types=1);
​
/**
 * Sorguyu ANALYZE FORMAT=JSON ile gerçekten çalıştırır ve her tablo için
 * tahmini satır (rows) ile okunan satırı (r_rows) yan yana döndürür.
 * Yalnızca SELECT için kullanın: ANALYZE UPDATE/DELETE değişikliği yapar.
 */
function planRaporu(PDO $pdo, string $select, array $params = []): array
{
    if (!preg_match('/^\s*SELECT\b/i', $select)) {
        throw new InvalidArgumentException('Yalnızca SELECT analiz edilir.');
    }
​
    $stmt = $pdo->prepare('ANALYZE FORMAT=JSON ' . $select);
    $stmt->execute($params);
    $plan = json_decode((string) $stmt->fetchColumn(), true, flags: JSON_THROW_ON_ERROR);
​
    $satirlar = [];
    $yuru = function (mixed $dugum) use (&$yuru, &$satirlar): void {
        if (!is_array($dugum)) {
            return;
        }
        if (isset($dugum['table']['table_name'])) {
            $t = $dugum['table'];
            $satirlar[] = [
                'tablo'    => $t['table_name'],
                'erisim'   => $t['access_type'] ?? '?',
                'index'    => $t['key'] ?? null,
                'tahmin'   => $t['rows'] ?? null,
                'okunan'   => $t['r_rows'] ?? null,
                'sayfa'    => $t['r_engine_stats']['pages_accessed'] ?? null,
                'kapsayan' => $t['using_index'] ?? false,
            ];
        }
        foreach ($dugum as $alt) {
            $yuru($alt);
        }
    };
    $yuru($plan);
​
    return $satirlar;
}

Called with the query above while the right index was in place, it returned a single line: siparisler | range | index=ix | tahmin=7 | okunan=7 | sayfa=42 | kapsayan=hayır. It gave the same result with PDO's emulated and native prepared statements (ATTR_EMULATE_PREPARES on and off).

Warning: ANALYZE really runs the query. Test a heavy report with it on a production server and you have run that report twice. This function belongs in development and CI.

A Checklist for Picking the Magnet#

• Equality columns first, the range column last. = and IN up front; >=, BETWEEN, LIKE 'abc%' at the end. If the ORDER BY column is the range column, sorting comes free.
• If your query does not supply the first column, the index cannot seek. For WHERE b = ?, an index on (a, b, c) gets scanned end to end at best.
• Put rows and r_rows side by side. A big gap means stale statistics or a bad plan.
• Look at pages_accessed. Time changes from machine to machine; on the same data, pages touched do not.
• Re-measure queries with ORDER BY ... LIMIT after adding an index. The most expensive plan in this article came from exactly there.

A magnet finds the needle in the haystack. Hold it by the wrong end and you hug the hay along with it, and by the time you notice you are carrying 795,861 pages.

Stay curious, and let your queries speak in counters.