There is a composite index on the fields name and username. Will the index work when querying WHERE username = ?

If a composite index is created on fields (name, username), its effectiveness depends on the order of the fields in the index and the query conditions.

In most DBMSs, a composite index works efficiently if the WHERE clause uses fields starting from the first field of the index. In your case, if the index is created as (name, username), then a query with a condition only on username (WHERE username = ?) usually cannot utilize this index effectively because the first column (name) is not involved in filtering.

If the index is created as (username, name), then a query on username will use the index.

For example:

• Index (name, username) — query on username without name — index will not be used.
• Index (username, name) — query on username — index will be used.

If queries on username are frequent, it is advisable to create a separate index on this field or change the order of fields in the composite index.