ENUM and select performance

[Alex Pukinskis]

I'm working on a project that involves storing a lot of attributes for
individuals (think height, weight, eye color, hair color, skin tone,
etc.) and will later require searching on those attributes (i.e. return
all blue-eyed people with red hair).

I'm trying to figure out how to store all of these attributes in my MySQL database. I can think of two potential ways to do it:

1. Use the ENUM datatype for fields like eye color.

2. Create small tables for each attribute - i.e

create table eyecolor (
id int not null primary key auto_increment,
color varchar(10)
)

and insert the appropriate ID in an eyecolor field in the person table.

Can someone tell me what the performance implications are for each
approach? Is the ENUM datatype effectively doing the same thing behind
the scenes (that is, creating some hidden table somewhere and storing an
integer in the actual table)? Will select performance be equivalent
with both approaches - that is, would

SELECT name FROM person WHERE eyecolor = 'blue' AND haircolor = 'red'

where eyecolor and haircolor are both ENUM fields be equivalent in
performance to a non-normalized table using the varchar datatype, or
would it be equivalent to my second approach -

SELECT name FROM person WHERE eyecolor = 1 AND haircolor = 3

We'll be doing a lot of these queries with much larger WHERE clauses, so
I want to be careful not to make a mistake here.

Thanks,

-Alex




---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php