I'll counter this statement.
I've experienced a noticeable speed-up when I changed a primary key
column from CHAR(80) to VARCHAR(80). This was due to the reduction
in table size. I don't UPDATE the column, so record fragmentation
is not a problem for me.
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.
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. :-)
Jan
Kris Dahl wrote:
>
> on 8/21/00 8:27 AM, Claude Cormier at techsupport@stripped wrote:
>
> > Unless you need a small text column or the data entered will always have
> > a fixed number of characters, is there a good reason for not using
> > VARCHAR all the time instead of CHAR?
>
> The difference between CHAR and VARCHAR is fixed-width vs. variable width.
> But this is purely how it is stored. CHARs are simply padded with spaces
> when stored if they are not the full length. These spaces are not displayed
> when doing a SELECT. So as far as you are concerned, they act exactly the
> same.
>
> The big difference is speed. CHARs are hella faster than VARCHARS. By a
> lot. CHARs take up a bit more disk space. It's disk space vs. speed. We
> choose speed for our applications.
>
> -k
>
> --
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/php/manual.php" before
> posting. To request this thread, e-mail mysql-thread48449@stripped
>
> To unsubscribe, send a message to:
> <mysql-unsubscribe-jan.dvorak=veda.cz@stripped>
>
> If you have a broken mail client that cannot send a message to the above
> address(Microsoft Outlook), you can use http://lists.mysql.com/php/unsubscribe.php
