Alex, This is a specialised question! Unfortunately, in my experience, it comes down to the phrase YMMV (your mileage might vary) and thus requires you to experiment with your data/machine combinations/etc - good job MySQL makes this quite easy!
Can I assume that you have done your (manual) homework reading up about MySQL quirks and efficiencies? In which case it boils down to ENUM offering a storage efficiency over (say) tinytext fields (when multiplied by tens of thousands of rows). I think that you will find that any processing efficiencies are so minor that they disappear into the server's disk retrieval time variations (but every little helps...). The contrary thought is that tinytext makes for a more readable (raw data) table, and is likely to be more scaleable if more choices are added to a data item over time - remember ENUM hits the wall at 8 or 16 bits/enumeration values (and SETs also have 'knees' and 'shoulders' in their expansion possibilities) whereas many more choices would be available in two, three, or four character text strings - sensible abbreviation allowing. When I was young (and innocent) and storage was a premium commodity, we would try to structure the life out of systems, and thus ENUM what we could. These days the parameters have changed, and I think it is cheaper on dev and maint time to go for something (both code and data) that is more readable - and therefore 'faster'/cheaper/better... You mention having an eye-color table and then inserting IDs into the primary table. Yes you could do this. However every data-fetch will require a join, and the structure not save much storage (even multiplied up...). Consider having tables of eye-colors, etc, for data-validation purposes, but putting the full abbreviation, eg "BLU" into the primary table's field rather than a join-necessitating ID (multiplied by multiple criteria/each identification point being used). Does that cover your main points? Will be happy to toss around more of these ideas/thoughts... Regards, =dn > 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 > > --------------------------------------------------------------------- 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
