Composite
Index on (a, b, c). Supports WHERE a = X AND b = Y queries. Leftmost prefix matters. Not for arbitrary column combinations.
Advertisement
Covering
Index includes all query columns. Answer entirely from index. No table lookup. Faster reads.
Advertisement
Partial
Index only WHERE clause matches (WHERE deleted = false). Smaller. Faster. Only for that partial condition.