Skip to content
Tutorials Center
My learning

Topics

Lesson 32 of 40 ยท Indexes and Performance

EXPLAIN and Index Design in MariaDB

Read MariaDB EXPLAIN and ANALYZE output, understand type, key, rows and Extra, and design composite and covering indexes using the leftmost-prefix and sargable-query rules.

You cannot tune what you cannot see. EXPLAIN shows the query plan: which indexes MariaDB will use, in what order it joins tables, and roughly how many rows it expects to read. Learning to read it turns "this query is slow" into "this query scans 2 million rows because the index column order is wrong".

This lesson explains the EXPLAIN columns, then turns the diagnosis into index design: composite indexes, covering indexes and sargable conditions. The runnable examples use the in-browser engine's EXPLAIN QUERY PLAN, which has a simpler format but shows the same decisions (scan versus search, which index, sort step).

Running EXPLAIN

SQL
EXPLAIN SELECT * FROM orders WHERE customer_id = 7 ORDER BY ordered_on DESC LIMIT 10;
EXPLAIN FORMAT=JSON SELECT ...;     -- more detail
ANALYZE SELECT ...;                 -- actually runs the query, adds real numbers
ANALYZE FORMAT=JSON SELECT ...;

EXPLAIN only estimates. ANALYZE executes the statement and reports both the estimated rows (rows) and the measured ones (r_rows), plus time (r_total_time_ms) and filter efficiency (filtered against r_filtered). A large gap between estimated and real rows means stale statistics: run ANALYZE TABLE. Never run ANALYZE on a DELETE or UPDATE you do not want executed; use it on SELECT statements (or inside a transaction you roll back).

The columns that matter

Column What to look for
table tables in join order, top to bottom
type access method, best to worst: system, const, eq_ref, ref, range, index, ALL
possible_keys indexes that might help
key the index actually chosen (NULL = none)
key_len how many bytes of the index are used: shows how many columns of a composite index were used
rows estimated rows to examine at this step
filtered percent of those rows expected to survive the WHERE
Extra extra notes: see below

Reading type:

  • const / eq_ref: at most one row (primary key or unique lookup), ideal.
  • ref: an index lookup that can return several rows.
  • range: a range scan on an index (BETWEEN, <, IN).
  • index: a full scan of an index (cheaper than ALL, still reads everything).
  • ALL: full table scan. Fine for tiny tables or when you need most rows; a red flag on big tables.

Reading Extra:

  • Using index: covering index, the query was answered from the index alone without touching the table rows.
  • Using where: filtering happens after rows are read.
  • Using filesort: a separate sort step because no index gives the order; it is not necessarily a disk file, but it can be costly on large sets.
  • Using temporary: an internal temporary table (often for GROUP BY or DISTINCT on non-indexed columns).
  • Using index condition: index condition pushdown, filtering earlier.
  • Using join buffer: a join without a usable index on the joined table.

Step 1: the query without an index

SQL Plan before and after an index ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Before: a scan plus a temporary sort (USE TEMP B-TREE FOR ORDER BY). After: a search on department_id, and the order is read straight from the index, so the sort step disappears. In MariaDB the same change turns Using where; Using filesort into Using where with type: ref.

Composite index design rules

  1. Equality columns first, then the range column, then sort columns. For WHERE department_id = 3 AND salary > 50000 ORDER BY hire_date, the index (department_id, salary) handles the filter; hire_date still needs a sort, because after a range condition on salary the later columns are no longer in order. The best index depends on which part is selective and which sort you can skip.
  2. Leftmost prefix: (a, b, c) serves a, a,b and a,b,c, but not b or c alone, and not a + c beyond the a part.
  3. Stop at the first range: columns after a range condition in the index cannot be used to narrow the search.
  4. Selectivity: put the more selective column earlier only when both are equality tests and no sort is involved. Cardinality in SHOW INDEX helps.
  5. Match the sort: ORDER BY a, b needs both columns in the same direction (descending indexes work from MariaDB 10.8) in the same order.

Verify with key_len: for INT NOT NULL columns each is 4 bytes, so key_len = 8 means the first two columns are used.

SQL Leftmost-prefix rule ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Covering indexes

If every column the query needs is in the index, the engine never reads the table row: that is Using index in MariaDB. In InnoDB every secondary index contains the primary key, so (department_id, salary) also covers queries that select id. Covering is a powerful way to speed up hot read paths, at the price of a wider index.

SQL A covering index ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The plan says USING COVERING INDEX (the same idea as Using index).

Sargable conditions

A condition is sargable (Search ARGument ABLE) when the engine can use an index to evaluate it. Applying a function to the indexed column, or mixing types, makes the condition non-sargable.

Slow (non-sargable) Fast (sargable)
WHERE YEAR(hire_date) = 2024 WHERE hire_date >= '2024-01-01' AND hire_date < '2025-01-01'
WHERE LOWER(email) = '[email protected]' compare directly under a case-insensitive collation
WHERE salary * 1.1 > 80000 WHERE salary > 80000 / 1.1
WHERE name LIKE '%son' LIKE 'Ma%' or a FULLTEXT / reversed-column index
WHERE phone = 5551234 (phone is VARCHAR) WHERE phone = '5551234'
WHERE a = 1 OR b = 2 two indexes (MariaDB can use index merge) or a UNION

MariaDB 10.2+ also supports generated columns with an index, a clean fix for "search by a derived value":

SQL
ALTER TABLE users
  ADD email_domain VARCHAR(100) AS (SUBSTRING_INDEX(email, '@', -1)) PERSISTENT,
  ADD INDEX idx_email_domain (email_domain);
SQL Function on the column blocks the index ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Joins

For each join MariaDB picks an order and, for the second and later tables, needs an index on the joined column. A missing index on the join column shows type: ALL with Using join buffer: the inner table is scanned once per outer row (or per buffer). Index the foreign key columns. If the plan joins in a bad order, STRAIGHT_JOIN or optimizer hints (see query tuning) can force it, but fix statistics and indexes first.

A tuning routine

  1. Find the slow query (slow query log, performance_schema, application metrics).
  2. Run EXPLAIN; look for ALL, Using filesort, Using temporary, huge rows.
  3. Check ANALYZE for estimated versus actual rows; run ANALYZE TABLE if they disagree.
  4. Fix the query first (sargable, select only needed columns, remove SELECT *).
  5. Add or reorder one index; test on realistic data size.
  6. Re-run EXPLAIN/ANALYZE, compare time, keep the change only if it helps.
  7. Check the write cost: an extra index slows INSERTs.

Common mistakes

  • Testing on a tiny table: the optimizer happily scans 100 rows; test with production-sized data.
  • Judging only by type: a ref on a poor index can still touch millions of rows. Look at rows.
  • Creating one index per column instead of one composite index that matches the query.
  • Forgetting that OR across different columns defeats single-index plans.
  • Trusting stale statistics.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In EXPLAIN, what does type = ALL mean?

    ALL reads the entire table. It is the worst access type for large tables.
  2. 2. Extra shows 'Using index'. What does that tell you?

    A covering index contains every column the query needs.
  3. 3. Which condition can use an index on hire_date?

    A bare column compared with constants is sargable. Functions on the column force a scan.
  4. 4. What does ANALYZE SELECT ... do that EXPLAIN does not?

    ANALYZE (MariaDB) runs the statement and adds r_rows and r_total_time_ms.

Summary

  • EXPLAIN shows the plan; ANALYZE adds real numbers. Read type, key, key_len, rows, Extra.
  • Design composite indexes with equality columns first, then range or sort, following the leftmost-prefix rule.
  • Covering indexes avoid table reads; sargable conditions keep indexes usable.
  • Tune in a loop: measure, change one thing, measure again.

Next: Query Tuning and the Slow Query Log