So what is a DB index? Think of it as an index in a physical book where you look at the index page to quickly get to where you want to get to. Some topics are organized beforehand so you can get to whatever you want to get to faster. But indexing also is not a cheap operation, it takes up space in addition to what the data already takes up so this has to be done with space as a consideration.
The topic we are discussing here is when is a good use case to index and when it's not. The simple rule of thumb is that when you read operations are heavy or if the database is read only you can index the hell out of your database table provided you have the additional space to accommodate the index. Imagine if you do a lot of write operations on your table, it's not the most effective if you have indexes or a lot of them on your table because these indexes have to be recalculated with every write.
The second major factor for indexing is based on cardinality. So to give you an example, if you have a database with a Gender field with 2 of maybe 3 genders in it, the cardinality of that is very low. Think of it analogous to the book example where you are finding the page which has the word THE and you will find so many of them. It's pointless to do something like that. So, the best ones to index are the ones which have a unique value and databases like MySQL I believe add an index on a primary key by default.
The third obvious one where indexing is needed is Search and Filter likelihood. If there is a high probability of searching and filtering based on an attribute, there is high value in indexing it.
well done dear