How do composite indexes differ from regular ones in structure and application in a database?

A composite index is an index created on multiple columns of a table simultaneously, unlike a regular index which is built on a single column.

Structure:

• A regular index stores values of one field and references to rows.
• A composite index stores ordered combinations of values from multiple fields.

Application:

• Composite indexes are effective for queries that filter or sort data across multiple columns at once.
• They can speed up searches based on multiple criteria, for example, WHERE col1 = ? AND col2 = ?.
• However, if a query uses only part of the fields from a composite index (starting from the first), the index can still be used.

Example:

CREATE INDEX idx_name ON table_name (column1, column2);

This index will help speed up queries filtering by column1 or by both column1 and column2 together, but not by column2 alone.