List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:July 8 1999 7:28pm
Subject:Re: varchar inserted instead of char?!
View as plain text  
Davor Cengija wrote:
> 
>         I'm creating a table:
> 
> mysql> create table hr_vijesti (
>         id mediumint unsigned primary key auto_increment,
>         datum_unosenja timestamp,
>         datum_odobravanja timestamp,
>         naslov char(255) not null,
>         nadnaslov text,
>         podnaslov text,
>         kategorija tinyint unsigned not null,
>         kljucne_rijeci char(255) not null,
>         autor char(64),
>         izvor smallint unsigned not null,
>         datum_stranica char(32),
>         slika_naslov smallint unsigned,
>         slika_body char(32),
>         vezane_vijesti char(64),
>         tekst mediumtext);
> Query OK, 0 rows affected (0.03 sec)
> 
>         after that i'm adding a column:
> 
> mysql> alter table hr_vijesti add column upisao_u_bazu char(32);
> Query OK, 0 rows affected (0.04 sec)
> Records: 0  Duplicates: 0  Warnings: 0
> 
>         but all char column types are turned into varchar(xx) where xx
>         is the number i've decided to use with char():
> 
> mysql> explain hr_vijesti;
>
> +-------------------+-----------------------+------+-----+---------+----------------+
> | Field             | Type                  | Null | Key | Default |
> Extra          |
>
> +-------------------+-----------------------+------+-----+---------+----------------+
> | id                | mediumint(8) unsigned |      | PRI | 0       |
> auto_increment |
> | datum_unosenja    | timestamp(14)         | YES  |     | NULL    |
> |
> | datum_odobravanja | timestamp(14)         | YES  |     | NULL    |
> |
> | naslov            | varchar(255)          |      |     |         |
> |
> | nadnaslov         | text                  | YES  |     | NULL    |
> |
> | podnaslov         | text                  | YES  |     | NULL    |
> |
> | kategorija        | tinyint(3) unsigned   |      |     | 0       |
> |
> | kljucne_rijeci    | varchar(255)          |      |     |         |
> |
> | autor             | varchar(64)           | YES  |     | NULL    |
> |
> | izvor             | smallint(5) unsigned  |      |     | 0       |
> |
> | datum_stranica    | varchar(32)           | YES  |     | NULL    |
> |
> | slika_naslov      | smallint(5) unsigned  | YES  |     | NULL    |
> |
> | slika_body        | varchar(32)           | YES  |     | NULL    |
> |
> | vezane_vijesti    | varchar(64)           | YES  |     | NULL    |
> |
> | tekst             | mediumtext            | YES  |     | NULL    |
> |
> | upisao_u_bazu     | varchar(32)           |      |     |         |
> |
>
> +-------------------+-----------------------+------+-----+---------+----------------+
> 16 rows in set (0.00 sec)
> 
>         (sorry for the broken lines).
> 
>         if i try to force char() instead of varchar(), nothing's
>         happening:
> 
> mysql> alter table hr_vijesti modify column naslov char(255) not null;
> Query OK, 0 rows affected (0.02 sec)
> Records: 0  Duplicates: 0  Warnings: 0
> 
>         but:
> 
> naslov            | varchar(255)          |      |     |         |
> 
>         i don't understand what's happening.
> 
>         mysql 3.22.20a
>         linux redhat 6.0, glibc2.1, kernel 2.2.10, intel pentium
>         (anything else?)
> 
>         Thanks
> 
>         p.s. if such a case is explained in the manual, please point
>         the right chapter.
> 
> --

having a char without any var in it is beneficial only if your record is
fixed length. If your record is not going to be fixed length anyway,
there is not point in being forced to a fixed length - you get all the
disadvantages while not receiving any advantages. This is the rational
for MySQL to automatically convert char to varchar when other fields
force the record to be variable length. You have a couple of columns of
type text, which causes this behavior.

I would suggest moving your text stuff into a separate table - that will
allow you to still use advantages of fixed length records.

Another suggestion, somewhat off the subject. Often records have fields
that are searched often and the ones that are retrieved once the record
was found. If searches are more frequent than retrievals, I would place
the search fields in one table, and retrieval only fields in another.
There are cases when this approach would not give you much performance
improvement, but very often it will.
-- 
Sasha Pachev
http://www.sashanet.com/ (home)
http://www.direct1.com/ (work)
Thread
varchar inserted instead of char?!Davor Cengija8 Jul
  • Re: varchar inserted instead of char?!Paul DuBois8 Jul
  • Re: varchar inserted instead of char?!Sasha Pachev8 Jul