What difficulties may arise when using composite indexes in a database?

Composite indexes are indexes created on multiple columns of a table. They can introduce certain complexities:

• Order of columns matters. Queries efficiently use the index only if filtering or sorting starts with the first column of the index. If a query uses only the second or third column, the index might not be used.
• Increased index size. The more columns in the index, the larger it is and the longer it takes to update when data changes.
• Support complexity. Changing the table structure or query requirements may require rebuilding indexes.
• Potential slowdown of insert and update operations. Because composite indexes need to be updated when data changes.

Example: an index on (last_name, first_name) will be used for a query filtering by last_name, but not for a query filtering only by first_name.