Advice for index design

[JORGE MALDONADO]

I have a table of artists with fields like the ones below:

* Name

* Birthday

* Sex (male/female)

Our application offers a catalog of artists where a user can select a range of birthdays and/or sex. For example, a user can get an artists catalog for those  male artists who were born between May 1, 1970 and May 1, 1990 ordered by birthday and, within each birthday date, ordered by name. I can think of defining one index for birthday, one index for name, and one index for sex.  Also, I can think of defining a compound index for birthday + name. Also there could be a compound index for sex + name. Another option could be a compound index for birthday + sex + name. There are many possible combinations. What is a good index design approach? Maybe, setting simple separate indexes (one for each field) would work fine if I need to retrieve data in different combinatios, but I am not sure. Maybe compound indexes is better. I will very much appreciate your advice.

Respectfully,

Jorge Maldonado

[Gavin Flower]

W.r.t. sex what about those people who:

1. are neither
2. are both
3. not specified
   
4. don't want to tell you
5. have changed their gender mid career
   

About 0.5% children are born in the folowing categories:

1. ambiguous genitalia
2. both
3. none
4. genitalia that doesn't match their brain wiring
5. born looking like a female, but change to male at puberty
   

I once saw an article about an island were about 10% of males were born looking like a female, but changed to male at puberty.  It was so common and well known that parents simply changed their clothes renamed them, and started treating them as male.  So I did a bit of research, exact percentages depend on definitions & fashions at the time of birth and what research you read.  Fortunately, as far as I know, no one in my immediate family falls into this group.


Cheers,
Gavin