Composite indexes make sense for that sort of thing, but in this case I see no use for it. You should have already created a primary key or at least a unique index on the username column; this will also increase performance when querying that column.
You want to query off of username first because it will only return 1 row. Querying off of "confirmed" first would be bad, even if there was an index, because ~50% of the rows would be returned by the index, and then it must scan through those returned rows to find the username.
In general, do not put indexes on columns that have very few possible values (e.g., BIT fields).
So assuming you have a case-insensitive unique index on username, that's all you need for max performance. If you made username a primary key then it automatically adds a unique index.
-----------------------
MySQL 5.0 Certified DBA
MySQL 5.0 Certified Developer