Comparison
Clustered indexvsCovering index
Clustered index
you pick the primary key and quietly decide the physical order the rows will be stored in on disk forever.
An index whose leaf level is the table itself, so row order on disk follows the key. Range scans on that key become sequential reads, which is enormous; everything else becomes a secondary lookup. Choosing a random UUID as the clustered key scatters inserts across the whole file and is a classic self-inflicted write bottleneck.
Full entry →Covering index
you add the extra column to the index so the query is answered from the index alone and never touches the table.
An index containing every column a query needs, so the engine can skip the lookup back into the heap or clustered rows. It is often the difference between a fast query and a fast query that still does a million random reads. The trade is index size and write cost, both of which grow with every column you include.
Full entry →