You should create indexes for regularly used queries. Don't create indexes that don't have a use case for the queries being executed against a table. Don't guess at what indexes a table needs. Instead, you need to know the queries that are being executed, how often they are executed, and how important they are. Then you can decide how to create indexes based on the conditions used in the WHERE clause and by the columns used in the ORDER BY and GROUP BY clauses.

There is no right number of indexes on a table or columns in your indexes. Generally speaking, too few or too many indexes can make the performance of your database worse. This is why it's important to know what queries are being executed on the table so that you can understand best what would help the performance. Having only one index might work for some tables, but maybe some tables need five indexes. Try to keep the number of columns in an index to the minimum number required—for example, you don't want to add all the columns of your table to an index. An exception to this could be if a table only has a few columns.

Here are some important things to note about indexing: 

• You should create indexes for uniqueness (primary key) and referential integrity (foreign keys). This will help ensure your database's data integrity.
• Indexing doesn't speed up everything that happens in a database table. It won't speed up writes (the insertion, updating, and deletion of data); it only speeds up reading (querying data from the database), so you need to be careful that you don't add an index for reads that then slows down writes too much. Indexing slows down writes because the index has to be updated each time data is written. 
• The order of the columns in indexes matters. If you have the columns in the index written as managerid then yearid, and you want to filter on just yearid, then your query filtering on yearid might not even use the index. You may need an index that has yearid first or maybe as the only column in the index. Also, just filtering on managerid would give you good performance even if you didn't use yearid in the filter, since the query would use the managerid then the yearid indexes.
• Indexes can take a lot of storage. A clustered index shouldn't take upspace on disk since it's an ordering of the data that is already sitting on disk, but nonclustered indexes can take up quite a bit of room depending on the size of the table and the number of columns in the index.
• Indexing columns that contain a lot of nonunique values may not provide much performance improvement. If you have to decide between the columns to index, then you should choose a column with more variability, such as the first name rather than state names.