Re: MySQL Performance Example Was: Seriously, WTF?

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

On 11 May 2008, at 8:16 pm, Andy Wardley wrote:

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


If you're using trivial queries and don't like joins then
you don't  
need a database. Hence, mySQL.

Sorry, just couldn't resist that (But there may be a grain
of truth  
in there 



Iain