I'm thinking about creating an index on a table like this:
primary | type | id 1 a 10001 2 a 10002 3 b 10001 n b 1....
type will contain four values, ~ 95 % will be "a" (or 1, please don't care about the exact value/normalization). Also, 95 % of all queries will be looking for "a", too, in combination with a list of ids (~ one to five or so), like:
SELECT id FROM table WHERE type = 'a' AND id IN (...)
Basic task is to check a list of given id's for existence, so a "covering index" is the thing I basically want.
Now, as far as I understood, it's better to have the column first which reduces the result set most, which would be id. But on the other hand, an intelligent database engine could detect that most of the queries are about "a" and could keep only this branch of the index tree in memory, or, enough memory provided, all three indices as a "different" index each, as it would be better not to bother with all the "b" values.
Is a MySQL/MariaDB (just InnoDB backend) doing this kind of optimizations? Or any other thoughts? I'm not so interested in this table, more a general view on this case.
Thx in advance for all your comments ;)