MySQL Performance Example Was: Seriously, WTF?
abuse at cabal.org.uk
Fri May 9 13:53:49 BST 2008
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.
More information about the london.pm