MySQL Performance Example Was: Seriously, WTF?

[Robbie Bow]

On Fri, May 9, 2008 at 11:02 AM, Christopher Jones <c.jones at ucl.ac.uk> wrote:

>
> Since `chr` is a set of defined values, at some point I did have it as an
> ENUM. But then I read somewhere that this is bad, so it got changed to
> VARCHAR. I always assumed that VARCHAR was better than CHAR, and that the
> length didn't matter because it was variable....! So is ENUM bad? Would it
> be better/worse than CHAR?
>

Personally, I think ENUM is good for known sets. The advantage of CHAR
over VARCHAR is fixed width records making seeking the rows much
faster. The advantage over ENUM is less maintenence if some new
possible value needs to be added to the data. The big and easy win is
getting fixed width records. ENUM is a micro-performance enhancement
in most cases.

>
> Its unlikely any one query will ever be run twice. Most queries would
> specify a `chr` and a `location`, so there will be plenty of queries
> 'overlapping' in the block of data they'll return, even though they're
> unlikely to be exactly the same. Would the cache help in such circumstances,
> or only when an identical query has been cached?
>

The query_cache would only be used when an identical query is run
again and the data hasn't changed, so not of much benefit in your
scenario. Your general key_buffer_size setting might be of more use if
you can get a big enough key_cache to maintain your indexes in cache,
but I suspect you're going to get little gain compared to having the
right indexes and fix width rows.