InnoDB stores every index, the table included, as a B+tree of 16 KiB pages. Leaves hold entries in key order, linked both ways; internal pages hold a separator key and a child page number per entry. A lookup reads one page per level; with hundreds of children per page the tree stays shallow. A range descends once, then walks the leaves.
SELECT ... INTO @var keeps results off the screen, so only the timings print:
SELECT total INTO @t FROM big_orders WHERE id = 567890;
SELECT COUNT(*), SUM(total) INTO @n, @s FROM big_orders WHERE customer_id = 4242;Query OK, 1 row affected (0.000 sec) Query OK, 1 row affected (0.166 sec)
The key lookup was too quick to time; the scan of the unindexed customer_id read every leaf, and only it grows with the table. After ANALYZE TABLE, mysql.innodb_index_stats counts 2,399 leaf pages for PRIMARY, about 417 rows each: 15/16 full, because rows arrived in key order (random inserts split pages and leave them half to 15/16 full). Page 4 of the .ibd file is the root, and sudo xxd -s $((4 * 16384 + 64)) -l 2 on it prints PAGE_LEVEL, 0002: three levels.