MENU


Covering Indexes

An index covers a query that uses only its columns, skipping the second descent; plans say Using index or "Covering index". Every entry holds the primary key, so (customer_id, status, ordered_at) already covers SELECT id, ordered_at, and appending id duplicates it.

The 100,000 orders of Section 3.11.2, without and with a covering indexSQL
SELECT COUNT(*), SUM(total) INTO @n, @s FROM big_orders WHERE customer_id <= 5000;
SELECT COUNT(*), MAX(ordered_at) INTO @n, @m FROM big_orders WHERE customer_id <= 5000;
Output
Query OK, 1 row affected (0.216 sec)
Query OK, 1 row affected (0.021 sec)

SUM(total) needs the rows, so the optimizer scanned the table; MAX(ordered_at) read only the index, ten times faster. SELECT * never can.