There is no perfect answer. A possibly good answer is...
First, are the columns compared with = constant? Or a "range", such as > constant? For now, I will assume only =.
How selective are the values? True/false values are not; let's ignore them.
Assuming the 'worst' case (always =, all are selective), let's take the 6 permutations of two columns:
INDEX(a, b) -- for (a), (a,b), (a,b,c), (a,b,d), (a,b,c,d)
INDEX(a, c)
INDEX(a, d)
INDEX(b, c)
INDEX(b, d)
INDEX(c, d)
This list will handle all the one- and two-column tests efficiently, and at least help when you use 3 or 4 columns.
If some column is always used in a range, then never put anything after it in the INDEX. If, for example, b is always tested via a range, change the 4th and 5th to (c,b) and (d,b)
Other tips:
- You say
INT. That allows 4 billion values and takes 4 bytes. Use a smaller datatype. 40M distinct values needs INT UNSIGNED, but if you have something with only a million distinct values, consider MEDIUMINT UNSIGNED (3 bytes, 0..16M). Etc. The rationale is that making the table and indexes smaller helps decrease I/O, hence improves speed.
- Do you need
id? Or is some combo of the columns unique and could become the PK, there by elimination id?
- If there are common patterns, extend some of the indexes. For example, if usually when users test
a and c, they also test b, then change (a, c) to (a, c, b).
- Use InnoDB.
- If any column is "low cardinality", then no index will be used when the
WHERE clause mentions only that column; live with it. That column, when combined with others, works fine.
- Do not over-normalize. I hope that a,b,c,d are not normalizations of floats or datetimes. Note how this hooks into my comments about "ranges".
If you would like to discuss further, please provide some extra clues about the data and queries involved. (Note that I had to do hand-waving about flags and ranges.)
