MySQL Performance Example Was: Seriously, WTF?

[Peter Corlett]

On 9 May 2008, at 11:02, Christopher Jones wrote:
[...]
> The `chr` field is one of the 24 chromosome names, (1..22, 'X',  
> 'Y'), and the `location` field is the position on the chromosome,  
> anywhere between 1 and ~250,000,000 for the biggest chromosome (1)  
> down to between 1 and ~50,000,000 for the smallest chromosome (Y).  
> The table is a fixed chunk of data for read only.

ENUM is only considered harmful in the cases where you are likely to  
need to add another possible value. Unless you are into serious  
genetic engineering, this isn't likely to happen in this case.

Using an ENUM will reduce the column's storage requirements to one  
byte. ISTR that MySQL will also silently change a small VARCHAR into a  
CHAR when creating a table if it reduces the storage requirements.  
However a CHAR(4) still takes four bytes whereas an ENUM takes just one.

It's also a good idea to mark columns as NOT NULL unless you actually  
expect to store and process NULL values. In MySQL this gives a slight  
performance boost and space reduction as well.

Note http://sql-info.de/en/mysql/gotchas.html#1_3 if you're using  
ENUM, especially as your particular application mixes integers and  
strings in the ENUM.

FWIW, PostgreSQL has an ENUM type as of version 8.3. It works, and  
doesn't have that MySQL gotcha.