MySQL Performance Example Was: Seriously, WTF?

[Andy Wardley]

Iain Barnett wrote:
> Have you all gone a bit doolally? You don't look at the numbers, you use 
> a join and look at the text. You could still use the numbers with an 
> enum, but you wouldn't, so why would you with a lookup table?

The point is that using a join for trivial queries (i.e. those quick tests
from the command line/db shell) is tedious:

Compare:

   SELECT name FROM people            	# short and sweet
   WHERE  sex='male';

vs

   SELECT name FROM people 		# short but not sweet
   WHERE  sex=1;

vs

   SELECT name FROM people, sexes 	# long but sweet
   WHERE  people.sex=sexes.key
   AND    sexes.value='male';

(where 'sweet' == not having to remember numerical IDs)

I'm not arguing specifically for or against ENUMs.  I use both ENUMs and
key/value tables depending on what's most appropriate for the task at hand,
But I do agree with the point that the flexibility of a separate lookup table
is offset by the inconvenience of having more complex queries.  If you're
never going to need to add new values then an ENUM is sufficient and probably
preferable IMHO.

A