How do you define indexes at the design stage of a database?

Question

MySQL

The table has columns:
id, uid, interval, data, oldstatus, newstatus, email
In the process, it turns out that we need indexes on id, uid, email composite on interval+data
Then there is a condition:
WHERE oldstatus != newstatus AND email=1
I I do not really understand the composite index on which fields is needed for such a request?
Already at least 5 indexes are coming out, and I cannot yet know which one will be used how often. How, under these conditions, not to miss some necessary index but also not to slow down the INSERT time too much when the database grows?
Or does it make sense not to make composite indexes yet, but to get by with a simple one for each column?

Answer 1

Indexes are generally put on those columns on which there is a selection where.
If your condition:
then bet on them.
As for the id, if you have it primary, then this is the index, you don’t need to add anything additional

Answer 2

Composite indexes are hung up in the order - we reduce selection - we select from the remained.
Those. if you have only two statuses and 1000 emails, then the index should be (email, status). Further by analogy.
How to choose something - looks at the functionality, and / or statistics. (Put a log on requests).
Also collect all the requests that are. View intersections, etc.