A. So here would I create a multi-column index called say startsends even though I have already created an index on starts already earlier?
Yes. But then drop the index on starts
. MySQL will use the multi-column index for starts
alone, provided that starts
is listed first in the index definition. (So if you had a query based on ends
, alone, you would need a separate index for ends
MySQL can only use partial multi-column indexes in the order the columns are defined.
Say you had an index on (col1, col7, col3). Then MySQL could use that index for col1. Or for col1 and col7. Or for col1 and col7 and col3. But it could not use it for col7 or col3 alone nor for col7 and col3 without col1. It's a fair stupid limitation, but if you are aware of it, you can plan for it.
B. The three column index will be the most efficient. But if you often make queries on (say) just starts
then you would want active
to be a separate index, as describe above.
*EXCEPT*. Except if active is a 1/0 (true/false) column, it doesn't really make sense to index it separately if there are roughly the same number of 1's and 0's. You would only index it if, say, there were only 5% 1's and you often queried for the 1's. There would be no point in indexing it if there were 95% 1's and you almost always queried for 1's: You will end up having to fetch 95% of the full records, anyway. In general, I don't index boolean fields. There are exceptions, and tacking a boolean onto the end of a multi-column key can and does make sense.
C. Kind of too general a question. If, say, 70% of your records have age > 31, then there's no point in indexing, at all. If 5% do, then yes. And whether it is multi-column or not depends on what other queries you will do.