> CHARs can be faster than VARCHARs if the following conditions are met:
> 1. You didn't do OPTIMIZE TABLE often enough;
> 2. The table can fit in the O/S buffer cache anyway.
> If the table (plus a substantial part of its index)
> doesn't fit in the core, disk speed becomes the hurdle
> and then, it's pretty much obvious that the less data you have
> to pass through the core, the sooner a query is finished.
I have found that even immediately after a optimize, on smaller tables at
least (I think I tested it on a 30k row table) converting from VARCHAR->CHAR
was still significantly faster. Your millage will obviously vary depending
on the application.
> If your MySQL is CPU-bound, try VARCHAR -> CHAR.
> If your MySQL is I/O-bound, try CHAR -> VARCHAR.
> If a column takes values from a short, constant list
> of distinct values, you'll be best off with an ENUM.
> They are by far the fastest strings I've seen. :-)
Actually, even if you have a fairly long list of distinct values ENUM would
be the way to go because it stores them as integers if memory serves. I am
not sure what the threshold would be, however.
-k
