Choose the first field in a Compound Index

It looks like choosing the first field in a compound index is very important.I am assuming there are some best practices on how to identify that first field. for example: if i have two queries one for first name and another for (first name, and last name).,then choosing a first name is the one i should do, is not it?
also I feel this may be also related to the nature of data. for example let us assume for every last name their will be about 100 first names, and for every first name there will be 1000 last name. which one should i chose as my first field or may be i should not have a compound index at all. like I can say I will use last name as an index and that will not impact may performance a lot since the maximum number of document is 100 for first name. This will give me an advantage in case the first name field is changing a lot.
sorry for the long question, but it looks like choosing first field of the index is not a trivial task. any best practices or rules to consider when choosing which field should be the first?

@Mohammad_89759

Sorry, but I don’t really understand your question. In general, you build your indexes to match your queries. So if you have, for example, a lot of queries on db.collection.find( { "last_name": <last>, "first_name": <first> } ) then you might create the index {"last_name": 1, "first_name": 1}. So you would identify the query requirements first, which will automatically define the first field. It’s up to you when you build the index how you structure that.

compound indexes are not free they come with a price in terms of storage size, and update operation. the more fields I have on the index the more complex it will be. The storage size issue will have an impact on the index performance. can you provide some information on that effect, and how indexes are loaded on memory and processed?

@Mohammad_89759

First of all, all indexes have performance costs, not just compound indexes. The various parameters are covered in the Lectures “Resource Allocation for Indexes” parts 1-3.

Overall, there is no simple “rule of thumb” that gives you any help on this; each situation is pretty much unique, Lucky for us, as otherwise there wouldn’t be so much demand for DBAs and consultants. :wink: