> ...where second_index_col operator val and first_index_col oper val etc...
The order in the WHERE clause does not matter. However the "operator" does matter -- mostly whether it is "=" or something else.
WHERE a op const
INDEX (a) -- good
INDEX (a, b) -- good
INDEX (b, a) -- not useful
WHERE a = 1 AND b op const
INDEX (a) -- good
INDEX (a, b) -- good (better)
INDEX (b, a) -- good if op is "="; else won't use the "a" part of the index
WHERE b op const AND a = 1
Same as above (order in the WHERE clause does not matter)
Look at it this way...
The first field(s) in the INDEX need to have "=" in the WHERE clause. After that, _one_ more field in the index can be used (with other OPs in the WHERE clause)
Caveats:
IN (const, const, ...) -- sometimes acts like "="; sometimes acts like a non-"=" OP.
In _rare_ cases "index merge" will use multiple indexes.
In newer versions of MySQL, the optimizer can "leap frog". This is a likely example:
INDEX(a,b)
WHERE a IN (1,3,5) AND b = 4
(Still, INDEX (b,a) would be better)
You mentioned "collate". If you are referring to CHARACTER SET and COLLATION, then it is best to have the fields and constants in the same CHARACTER SET and COLLATION.
Table a: x CHARACTER SET latin1
Table b: x CHARACTER SET utf8
FROM a JOIN b USING(x) -- may perform poorly
Table a: x CHARACTER SET utf8 COLLATE utf8_general_ci
Table b: x CHARACTER SET utf8 COLLATE utf8_unicode_ci
FROM a JOIN b USING(x) -- may perform poorly
Some more index stuff:
http://mysql.rjweb.org/doc.php/index1