Dev Learning Lab

The labs on this site need JavaScript to run. The same content is written out below.

Learn › Database › Indexes and execution plans

Composite and covering indexes

A composite index can be used only in column order

An index on several columns is sorted by the first column, then by the next column, and so on. So the WHERE conditions must start from the first column to narrow the search range, and once a range condition is reached, the columns after it cannot be used to narrow the range. When every column the query reads is in the index, so that the table does not have to be visited, the index is called a covering index for that query.

Checked against documentation and standards · The official MySQL documentation

Concepts in this lab

Composite index and leftmost prefix
A composite index is an index built on two or more columns together. It is sorted by the first column first and then by the second column within that, so it can be used for a search only when the conditions use its columns in order, starting from the left. An index on (a, b, c) is used for a condition on a or on a and b, but not for a condition on b alone (as an exception, in MySQL 8.0.13 and later the optimizer can use this index with Skip Scan when the leading column has few distinct values).
Covering index
An index that contains every column a query needs, so the result can be produced from the index alone without reading the table rows. Skipping the table access makes the read faster. In a MySQL execution plan, it appears as Using index in the Extra column.
Clustered index and secondary index
A clustered index stores the row data itself at the bottom of the index (the leaf nodes), so the data of the table is stored in the order of its key. In MySQL InnoDB, the primary key is the clustered index. An InnoDB secondary index holds the primary key value in its leaf nodes. After a lookup in the secondary index, that primary key is used for one more lookup in the clustered index to read the row.
Cardinality and selectivity
Cardinality is the number of distinct values in a column. The more distinct values there are (for example, a national ID number or an email address), the fewer rows one condition leaves and the more effective an index is. The fewer kinds of values there are (for example, gender), the more rows match a condition and the less effective an index is. Selectivity is the cardinality divided by the number of rows in the table; the closer it is to 1, the better a single condition narrows down the rows.
Query execution plan (EXPLAIN)
The plan that the database decides on for how to run a query. It shows which indexes are used, in what order the tables are joined, and how many rows the database expects to read. You view it by putting EXPLAIN in front of the query, and it is the starting point for finding the cause when a query is slow.

Try these

Running with the default settings

  1. The orders table has 1,000,000 rows. The index is sorted in the order member_id → status → created_at. Entries with the same first column are grouped together, within that group they are in order of the second column, and within that in order of the third column.
  2. Column #1 (member_id): there is the condition member_id = 7, and it is the first column of the index, so this condition narrows the range to read.
  3. Column #2 (status): there is the condition status = 'PAID', and the columns before it are all fixed, so this condition narrows the range to read.
  4. Column #3 (created_at): there is the condition created_at >= '2026-09-01', and the columns before it are all fixed, so this condition narrows the range to read.
  5. 50 index entries in the narrowed range are read.
  6. SELECT * needs columns that are not in the index too. For each of the remaining 50 entries, the query goes to the table (the clustered index) and reads the whole row.

Result of this run

Code

CREATE INDEX idx_orders ON orders (member_id, status, created_at);

SELECT *
  FROM orders
 WHERE member_id = 7
   AND status = 'PAID'
   AND created_at >= '2026-09-01';

Assumptions of this model

This is a calculation that assumes that, in a table of 1,000,000 rows, member_id = 7 matches 0.1% of all rows, status = 'PAID' matches 50%, and the date condition matches 10%. The rules that index columns must be used from the left and that the columns after a range condition cannot narrow the search range follow the description of composite indexes in the official MySQL documentation. Filtering conditions at the index stage after the order has been broken (Using index condition) is the index condition pushdown behavior of MySQL. The real optimizer may choose a different plan depending on statistics, and here it is assumed that this index is used. This was not checked against the EXPLAIN output of a real database.

Previous lab: Indexes and full table scans · Next lab: Reading the execution plan

Concepts in this lab

What this lab shows

Settings

Code


      
    


    

      
Diagram

      

      

    


    

      

      

      

    


  


  

    

      
Run comparison

      

      

    

    

      
Try these