MySQL Performance Example Was: Seriously, WTF?

[David Cantrell]

On Sat, May 10, 2008 at 03:05:03PM +0100, Iain Barnett wrote:
> >On 10 May 2008, at 14:27, David Cantrell wrote:
> >>An ENUM is self-documenting.
> There are 2 things needed for any piece of documentation, the What,  
> and the Why.

That depends entirely on what the user is doing.  Quite often when I'm
debuggering someone elses code all I care about is What.  I don't care
Why they did something, because I already know that What they've done is
wrong.  Conversely, if I'm trying to make someone elses code run faster
or run on a different platform, I care more about Why they did various
things than about What they did.

> How does an enum self-document if there are (at least) 4 reasons for  
> it's use?
> 
> 1. Because (I believe) this is a fixed list

CMYK is a fixed list.  Now, I agree that you might later need to add
colours like silver and gold that can't be represented in CMYK-space,
but at that point you're probably going to stop using CMYK altogether
and switch to Pantone, which is a rather bigger change to your model
than merely using a lookup table instead of an ENUM.

> 2. Because (I believe) it's faster than a lookup table

It's faster *for a person*.

> Data dictionary + data model + bit of explanation as to *why* things  
> are done = documentation.
> Enum ain't documentation any more than a table is 'self' documenting  
> or a field name is self documenting.

Feel free to believe that if you like.  I, however, believe that using
an ENUM is a valid way of avoiding saying "the string in field 'name' in
the row with id 2 in table 'list_of_pigments'", much like calling a
variable 'query' is a more convenient way of saying "the string which
starts at address 0xBEEFCAFE".

Using an ENUM like that is like using meaningful variable names.  It
isn't enough documentation for all tasks, but it *is* enough for some
tasks.

> >And one fewer tables to join on. I'd say go table first and move to  
> >enum
> >as part of optimising denormalisation. But maybe that's just me.
> Joins aren't expensive on fixed width fields.

Not for the database.  They are damned annoying for a person writing an
ad-hoc query though.  And used in the right place, an ENUM isn't even a
denormalisation (relational database theory pedants are requested to
stay under their bridges at this point).

-- 
David Cantrell | Nth greatest programmer in the world

       23.5 degrees of axial tilt is the reason for the season