Performance of VARCHAR versus CHAR

[Andreas Meyer]

Hi,

Does anybody have experience with the performance of CHAR in
comparison with VARCHAR? I´ve converted the table from varchar
(dynamic format) to char (fixed row length) and the total size of the
data doubles (not counting the keys).

The database structure is as follows, the database has about 0.5 mio.
entries right now with many intensive queries. As I have heard, fixed
rows are faster.. but what about the influence of the total disc space
on performance?

Tanks,

Andreas

--- Table-Structure:

CREATE TABLE descr (
id int(11) unsigned NOT NULL auto_increment,
descr char(100) NOT NULL default '',
seclevel smallint(4) NOT NULL default '0',
submitter char(55) NOT NULL default '0000',
time datetime NOT NULL default '0000-00-00 00:00:00',
userid mediumint(6) unsigned NOT NULL default '0',
cat mediumint(6) unsigned NOT NULL default '0',
thumb_x tinyint(3) unsigned NOT NULL default '0',
thumb_y tinyint(3) unsigned NOT NULL default '0',
points smallint(4) NOT NULL default '0',
channel smallint(4) NOT NULL default '1',
active enum('n','p','x','y','z') NOT NULL default 'y',
pcat mediumint(6) unsigned NOT NULL default '0',
movechannel smallint(4) NOT NULL default '0',
movecat mediumint(6) unsigned NOT NULL default '0',
movetime datetime NOT NULL default '0000-00-00 00:00:00',
comments smallint(4) NOT NULL default '-1',
ptime datetime NOT NULL default '0000-00-00 00:00:00',
parserver tinyint(3) unsigned NOT NULL default '0',
fflag enum('0','u','v','p','h','1','2','3') NOT NULL default '0',
language enum('0','1','2','3','4') NOT NULL default '0',
PRIMARY KEY (id),
KEY userid (userid),
KEY channel (channel),
KEY pcat (pcat),
KEY movechannel (movechannel),
KEY movecat (movecat),
KEY movetime (movetime),
KEY ptime (ptime),
KEY time (time),
KEY catview (cat,active,time)
) TYPE=MyISAM ROW_FORMAT=FIXED;

[username]

I can't answer your question, but I have also done something similar
-- making sure a table that used to use VARCHAR used only fixed length
CHAR. This is on a database that gets about 200 updates per second on
avg. Even with everything being fixed length, the database still needs
to be optimized once in a while because despite the fixed lengths --
it still gets fragmented.

On 5 Nov 2003 05:11:38 -0800, ma**@fotocommunity.de (Andreas Meyer)
wrote:

Hi,

Does anybody have experience with the performance of CHAR in
comparison with VARCHAR? I´ve converted the table from varchar
(dynamic format) to char (fixed row length) and the total size of the
data doubles (not counting the keys).

The database structure is as follows, the database has about 0.5 mio.
entries right now with many intensive queries. As I have heard, fixed
rows are faster.. but what about the influence of the total disc space
on performance?

Tanks,

Andreas

--- Table-Structure:

CREATE TABLE descr (
id int(11) unsigned NOT NULL auto_increment,
descr char(100) NOT NULL default '',
seclevel smallint(4) NOT NULL default '0',
submitter char(55) NOT NULL default '0000',
time datetime NOT NULL default '0000-00-00 00:00:00',
userid mediumint(6) unsigned NOT NULL default '0',
cat mediumint(6) unsigned NOT NULL default '0',
thumb_x tinyint(3) unsigned NOT NULL default '0',
thumb_y tinyint(3) unsigned NOT NULL default '0',
points smallint(4) NOT NULL default '0',
channel smallint(4) NOT NULL default '1',
active enum('n','p','x','y','z') NOT NULL default 'y',
pcat mediumint(6) unsigned NOT NULL default '0',
movechannel smallint(4) NOT NULL default '0',
movecat mediumint(6) unsigned NOT NULL default '0',
movetime datetime NOT NULL default '0000-00-00 00:00:00',
comments smallint(4) NOT NULL default '-1',
ptime datetime NOT NULL default '0000-00-00 00:00:00',
parserver tinyint(3) unsigned NOT NULL default '0',
fflag enum('0','u','v','p','h','1','2','3') NOT NULL default '0',
language enum('0','1','2','3','4') NOT NULL default '0',
PRIMARY KEY (id),
KEY userid (userid),
KEY channel (channel),
KEY pcat (pcat),
KEY movechannel (movechannel),
KEY movecat (movecat),
KEY movetime (movetime),
KEY ptime (ptime),
KEY time (time),
KEY catview (cat,active,time)
) TYPE=MyISAM ROW_FORMAT=FIXED;

[username]

I can't answer your question, but I have also done something similar
-- making sure a table that used to use VARCHAR used only fixed length
CHAR. This is on a database that gets about 200 updates per second on
avg. Even with everything being fixed length, the database still needs
to be optimized once in a while because despite the fixed lengths --
it still gets fragmented.

On 5 Nov 2003 05:11:38 -0800, ma**@fotocommunity.de (Andreas Meyer)
wrote:

Hi,

Does anybody have experience with the performance of CHAR in
comparison with VARCHAR? I´ve converted the table from varchar
(dynamic format) to char (fixed row length) and the total size of the
data doubles (not counting the keys).

The database structure is as follows, the database has about 0.5 mio.
entries right now with many intensive queries. As I have heard, fixed
rows are faster.. but what about the influence of the total disc space
on performance?

Tanks,

Andreas

--- Table-Structure:

CREATE TABLE descr (
id int(11) unsigned NOT NULL auto_increment,
descr char(100) NOT NULL default '',
seclevel smallint(4) NOT NULL default '0',
submitter char(55) NOT NULL default '0000',
time datetime NOT NULL default '0000-00-00 00:00:00',
userid mediumint(6) unsigned NOT NULL default '0',
cat mediumint(6) unsigned NOT NULL default '0',
thumb_x tinyint(3) unsigned NOT NULL default '0',
thumb_y tinyint(3) unsigned NOT NULL default '0',
points smallint(4) NOT NULL default '0',
channel smallint(4) NOT NULL default '1',
active enum('n','p','x','y','z') NOT NULL default 'y',
pcat mediumint(6) unsigned NOT NULL default '0',
movechannel smallint(4) NOT NULL default '0',
movecat mediumint(6) unsigned NOT NULL default '0',
movetime datetime NOT NULL default '0000-00-00 00:00:00',
comments smallint(4) NOT NULL default '-1',
ptime datetime NOT NULL default '0000-00-00 00:00:00',
parserver tinyint(3) unsigned NOT NULL default '0',
fflag enum('0','u','v','p','h','1','2','3') NOT NULL default '0',
language enum('0','1','2','3','4') NOT NULL default '0',
PRIMARY KEY (id),
KEY userid (userid),
KEY channel (channel),
KEY pcat (pcat),
KEY movechannel (movechannel),
KEY movecat (movecat),
KEY movetime (movetime),
KEY ptime (ptime),
KEY time (time),
KEY catview (cat,active,time)
) TYPE=MyISAM ROW_FORMAT=FIXED;