Re: MySQL Performance Example Was: Seriously, WTF?

On 10 May 2008, at 14:27, David Cantrell wrote:

> On Sat, May 10, 2008 at 04:30:57AM +0100, Iain Barnett
wrote:
>> On 9 May 2008, at 5:07 pm, David Cantrell wrote:
>>> I've always treated ENUM as being more a
reminder to myself and to
>>> whoever has to maintain my stuff about what the
column is for.  eg,
>>> that
>>> the 'pigment' field can contain cyan, magenta,
yellow or black but
>>> nothing else.
>> Um, documentation? You know, that thing no one
likes to do. [1]
>
> Oh, yeah, documentation.  The thing that you have to
open in another
> window, search through, and so on - if you even stop to
think that you
> might need to refer to it.
>
> An ENUM is self-documenting.


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.

-- 
Dave Hodgkinson                                MSN:
davehodghotmail.com
Site: http://www.davehodgkins
on.com                   UK: +44 7768 49020
Blog: http://davehodg.blogspot
.com                    NL: +31 654 982906
Photos: http://www.flic
kr.com/photos/davehodg

On 10 May 2008, at 1:40 pm, Dave Hodgkinson wrote:

> On 10 May 2008, at 14:27, David Cantrell wrote:
>
>> On Sat, May 10, 2008 at 04:30:57AM +0100, Iain
Barnett wrote:
>>> On 9 May 2008, at 5:07 pm, David Cantrell
wrote:
>>>> I've always treated ENUM as being more a
reminder to myself and to
>>>> whoever has to maintain my stuff about what
the column is for.  eg,
>>>> that
>>>> the 'pigment' field can contain cyan,
magenta, yellow or black but
>>>> nothing else.
>>> Um, documentation? You know, that thing no one
likes to do. [1]
>>
>> Oh, yeah, documentation.  The thing that you have
to open in another
>> window, search through, and so on - if you even
stop to think that  
>> you
>> might need to refer to it.
>>
>> An ENUM is self-documenting.
>


There are 2 things needed for any piece of documentation,
the What,  
and the Why. Nothing about a datatype, or code in general
explains  
the reason behind a decision (although in simple cases it
can be  
inferred), and that is usually the most important aspect
behind what  
was done.

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
2. Because (I believe) it's faster than a lookup table
3. It was used because of a misconception about what an enum
should  
be used for
4. It uses less storage space
5. Some combination of the above

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.

> 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.

Iain