Re: Advice for index design

On 11/04/13 10:30, JORGE MALDONADO wrote:
> 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
>

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