# MariaDB Composite Index: Hold the Magnet by the Right End > I ran one query through ANALYZE against five indexes on a million-row MariaDB table. A composite index in the wrong column order was three times slower than no index; the right order read 7 rows, and a covering index never touched the table. - Author: [Halit Yeşil](https://halityesil.com/en/about/) - Published: 2026-10-10 - Language: English - Canonical URL: https://halityesil.com/en/mariadb-composite-index-order/ - Turkish version: https://halityesil.com/mariadb-index-optimizasyonu.md - Category: MySQL / MariaDB - Tags: composite index, covering index, database performance, EXPLAIN command, InnoDB, MariaDB, MySQL performance, PHP, SQL query analysis - License: https://creativecommons.org/licenses/by-nc/4.0/ - Cite as: Halit Yeşil. “MariaDB Composite Index: Hold the Magnet by the Right End”. halityesil.com, 2026-10-10. https://halityesil.com/en/mariadb-composite-index-order/ --- Both the [N+1 article](https://halityesil.com/en/the-king-slayer-of-mysql-solving-the-n1-query-problem/) and the [million-row WHERE article (in Turkish)](https://halityesil.com/mysql-where-kosullari-sirasi-1-milyon-satirda-100-kat-performans-farki/) 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: ```sql 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](https://petrunia.net/2014/06/30/new-feature-in-mariadb-101-analyze-statement/) that they first planned to call it `EXPLAIN ANALYZE`, as PostgreSQL does, and dropped the name after objections. According to the [official documentation](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/analyze-and-explain-statements/analyze-statement), `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](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/analyze-and-explain-statements/analyze-format-json). **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. ```sql 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: ```sql 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 | Index | Plan | Estimated (rows) | Read (r_rows) | Pages | Time | |---|---|---|---|---|---| | None | ALL | 1,000,000 | 1,000,000 | 9,130 | 185 ms | | `(olusturma)` | ALL | 1,000,000 | 1,000,000 | 9,130 | 179 ms | | `(olusturma, musteri_id, durum)` | range | 493,596 | 265,171 | 795,861 | 539 ms | | `(musteri_id, durum, olusturma)` | range | 7 | 7 | 42 | 0.08 ms | | `(musteri_id, durum, olusturma, tutar)` | range, Using index | 7 | 7 | 3 | 0.03 ms | ### 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](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/optimization-and-indexes/compound-composite-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 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.