MySQL Performance Example Was: Seriously, WTF?

[Iain Barnett]

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