Covering Indexes vs. Composite Indexes in MariaDB: Not the Same Thing

I spent an embarrassing number of years thinking "composite index" and "covering index" were two names for the same concept. They're not. Once I actually internalized the difference - and more importantly, once I started reading EXPLAIN output with that distinction in mind - I rewrote parts of three different schemas and saw query times drop from the 80-200ms range down to under 10ms on tables that were already indexed. The indexes already existed. I just hadn't built them right.

The Definitions First, Then Why They Matter

A composite index is an index on more than one column. That's it. INDEX(last_name, first_name) is a composite index. MariaDB (and MySQL) will use it for lookups on last_name alone, or on last_name + first_name together, but not on first_name alone - the leftmost prefix rule applies.

A covering index is a different concept entirely. An index is "covering" for a particular query when every column the query touches - in SELECT, WHERE, JOIN, ORDER BY, GROUP BY - exists inside the index itself. MariaDB can satisfy the entire query from the index without ever touching the actual table rows (the heap or clustered index). In EXPLAIN output, you see Using index in the Extra column. That's the tell.

A composite index can be a covering index. But a single-column index can also be a covering index for the right query. And a composite index can exist that is definitely not covering. These are orthogonal ideas.

The Real-World Setup That Made This Click

I was working on a healthcare-adjacent scheduling application - not going to name the client, but it was a Seattle-area clinic management system. They had an appointments table, around 4 million rows, with columns something like this:

CREATE TABLE appointments (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id  BIGINT UNSIGNED NOT NULL,
  provider_id BIGINT UNSIGNED NOT NULL,
  location_id INT UNSIGNED NOT NULL,
  status      VARCHAR(20) NOT NULL,
  scheduled_at DATETIME NOT NULL,
  created_at  DATETIME NOT NULL,
  notes       TEXT
);

They had this index:

INDEX idx_provider_scheduled (provider_id, scheduled_at)

And a query that ran dozens of times per page load for the provider dashboard:

SELECT id, patient_id, status, scheduled_at
FROM appointments
WHERE provider_id = 42
  AND scheduled_at >= '2024-01-01'
  AND scheduled_at < '2024-04-01'
ORDER BY scheduled_at ASC;

EXPLAIN showed the composite index being used. Row estimates looked reasonable. But the query was taking 60-90ms consistently. I ran EXPLAIN properly this time and looked at the Extra column:

Using index condition

Not Using index. That phrase Using index condition means MariaDB is using the index to locate rows, but it still has to go back to the table to fetch columns that aren't in the index. In this case, patient_id and status weren't in the index. For every row the index found, MariaDB was doing a lookup back to the clustered primary key row to grab those two columns. On 4 million rows with a result set of a few hundred appointments, that's hundreds of random reads.

The Fix: Make the Index Covering

I dropped the old index and added this one:

ALTER TABLE appointments
  DROP INDEX idx_provider_scheduled,
  ADD INDEX idx_provider_scheduled_covering (
    provider_id,
    scheduled_at,
    status,
    patient_id,
    id
  );

Now EXPLAIN shows:

Using where; Using index

Using index means the query is satisfied entirely from the index. No table row lookups. Query time dropped to 4-8ms. Same query, same data, same hardware. Just a different index definition.

The id column at the end might look redundant since InnoDB secondary indexes already include the primary key internally - but I include it explicitly when I'm working with the index through application code or when I want the intent to be obvious to the next developer.

Reading EXPLAIN Output for This Specifically

Here's what to look for. Run your query with EXPLAIN prepended:

EXPLAIN SELECT id, patient_id, status, scheduled_at
FROM appointments
WHERE provider_id = 42
  AND scheduled_at >= '2024-01-01'
  AND scheduled_at < '2024-04-01'
ORDER BY scheduled_at ASC;

The columns that matter for this analysis:

• key - which index is being used
• rows - estimated rows examined (lower isn't always better; sometimes a covering index scan of 10k rows beats 200 random heap lookups)
• Extra - this is where the story is

Using index condition is the one to notice. It's not terrible - ICP was actually a big improvement over the older behavior - but it means you're still hitting the heap.

Gotchas That Will Bite You

Column order in a composite index matters for covering, not just for filtering. The leftmost prefix rule governs which queries can use the index at all. But for covering purposes, order doesn't matter - if all the columns are in the index, the query is covered regardless of which column comes first. I've seen developers obsessively reorder columns trying to fix a coverage problem when the fix is just adding the missing column.

TEXT and BLOB columns cannot be fully included in an index. If your query selects a TEXT column, you cannot make the index covering for that query. Full stop. In my example above, the notes TEXT column is why I was careful to only select id, patient_id, status, scheduled_at. If someone had written SELECT * in that query, the index could never be covering. SELECT * is a covering index killer.

Wider indexes cost write performance. Every column you add to an index is storage overhead and write overhead. On a table with heavy INSERT/UPDATE load, a 5-column covering index has real costs. On a read-heavy reporting table, the tradeoff almost always favors the covering index. Know your access patterns.

MariaDB's innodb_stats_persistent and analyze timing can mislead you. If you add an index and EXPLAIN still shows the old plan, run ANALYZE TABLE appointments; and check again. The optimizer sometimes needs fresh statistics to pick up a new index properly, especially on large tables.

Watch for implicit columns in InnoDB. InnoDB secondary indexes always include the primary key columns internally. So if your primary key is id BIGINT, and you're querying SELECT id FROM appointments WHERE provider_id = 42, an index on just (provider_id) is already covering for that query - even though id isn't listed in the index definition. This can be a pleasant surprise when you run EXPLAIN and see Using index on what looks like an incomplete index.

When I Reach for a Covering Index Intentionally

I design for covering indexes in these situations:

• High-frequency read queries - anything that runs on every page load or every API call. In Laravel this usually means queries inside ->each() loops or inside controller methods that have no caching layer.
• Reporting and aggregation queries - COUNT(), SUM(), GROUP BY across date ranges. These often scan large portions of a table and benefit enormously from never touching the heap.
• Pagination queries - the SELECT id, title, status, created_at FROM posts WHERE user_id = ? ORDER BY created_at DESC LIMIT 20 OFFSET 0 pattern. Cover it.
• Any query running against a table over ~500k rows where I don't control the query frequency.

I don't obsess over covering indexes for:

• Admin screens or infrequent reports where 200ms is fine
• Queries that fetch TEXT or BLOB columns - they can't be covered anyway, so focus on the WHERE clause index instead
• Small tables where a full scan is faster than index overhead

A Quick Laravel Snippet for Context

If you're adding these in a Laravel migration, here's how I write it:

Schema::table('appointments', function (Blueprint $table) {
    $table->dropIndex('idx_provider_scheduled');

    $table->index(
        ['provider_id', 'scheduled_at', 'status', 'patient_id'],
        'idx_provider_scheduled_covering'
    );
});

Then verify it worked by running the raw EXPLAIN in Tinker or through a DB::select call:

$plan = DB::select("
    EXPLAIN SELECT id, patient_id, status, scheduled_at
    FROM appointments
    WHERE provider_id = 42
      AND scheduled_at >= '2024-01-01'
      AND scheduled_at < '2024-04-01'
    ORDER BY scheduled_at ASC
");

dd(array_column($plan, 'Extra'));

You want to see Using index in that output. If you see Using index condition, something isn't in the index that needs to be.

The Schema Decision Rewrite

Here's the practical upshot: if you design your indexes thinking only about "what columns do I filter on," you'll end up with composite indexes that help but don't fully deliver. If you instead ask "what is the complete set of columns this query touches, and can I put all of them in the index" - you start designing covering indexes by habit.

That shift in thinking is what changed how I approach schema design. I now keep a short list of the 5-10 highest-frequency queries for any table I'm designing, and I make sure the indexes I build are covering for those specific queries. Everything else can deal with Using index condition.

Composite indexes aren't wrong. They're just incomplete if covering was an option and you missed it. EXPLAIN will always tell you which one you built.