How does a composite index work and when should you use it?

A composite (compound) index includes multiple columns. Column order matters - index is most effective when queries filter on leftmost columns (leftmost prefix principle). Index on (A, B, C) helps queries on A, (A,B), or (A,B,C), but not B alone or (B,C). Consider selectivity: place most selective columns first. Useful for queries with multiple WHERE conditions or ORDER BY on multiple columns. Covering index includes all query columns, avoiding table access.