473,698 Members | 2,235 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Performance of VARCHAR versus CHAR

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,tim e)
) TYPE=MyISAM ROW_FORMAT=FIXE D;
Jul 19 '05 #1
2 5050
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**@fotocommun ity.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,tim e)
) TYPE=MyISAM ROW_FORMAT=FIXE D;


Jul 19 '05 #2
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**@fotocommun ity.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,tim e)
) TYPE=MyISAM ROW_FORMAT=FIXE D;


Jul 19 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1053
by: Andreas Meyer | last post by:
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...
6
13133
by: pragile | last post by:
I have a stored procedure that has some problems with slow performance. The procedure has 2 parameters @firstname varchar(100) @lastname varchar(100) These parameters can have values like a%, that is wildcard searches. The strange thing is that if one of the parameters has the value %, and the other one a%, the performance is very bad. If i subsistute the variables with exactly the same values hardcoded in
5
10425
by: twkelsey | last post by:
Hi, My company has a scenario where we would like to change the data type of an existing primary key from an integer to a char, but we are concerned about the performance implications of doing so. The script for the two tables that we need to modify is listed below. Table FR_Sessions contains a column named TransmissionID which is currently an integer. This table contains about 1 million rows of data. Table FR_VTracking table also...
5
1614
by: hishamfangs | last post by:
Hi guys! I'm facing a problem and I can't quite figure it out! I have created a table on SQL Server 2000 to store call records. We get about 250,000 calls a day, and the most frequently used query retrieves all the calls records for the day and groups them by destination, so the query would return something like this: Destination Calls Minutes
2
1810
by: Schardosim | last post by:
HI, I am making the importation of 200.000 rows for one temporary table. To leave of this temporary table insert, updates and deletes for other tables are executed. However the reply time that I am time is very high, 24h to execute 200.000. My environment is AIX 433, with DB2 7.2, Fixpak 8. If somebody will have some tip to improve this performance, is grateful. Best Regards
3
2127
by: kenfar | last post by:
I've got a UDF that is used on the publishing side of a data warehouse: when the data is headed out to a mart. It works fine for sets of a few thousand rows, but really slows for sets of a few million rows: a query will go from one to thirty minutes due to two calls on each row. Any suggestions or advise? ----------------------------------------------------------------------------- -- convert ip from integer format to string format --...
2
1602
by: rAinDeEr | last post by:
Hi, I have a table with the following definition. The primary key is west_nme and west_eff_tms and i have created unique index on them. The foreign keys which reference other parent tables are west_typ_cde,r_cde,ccl_cde and t_cde and I have created indexes FK_t_west01,FK_t_west02,FK_t_west03 respectively. Now, all the other fields in this table participate in the WHERE clause
4
4283
by: Andrew S | last post by:
Hello Mr. Expert: - I have 3 tables in mysql in MyISAM table format, I am using mysql4.0 on freebsd5.3 - producttbl, productdetailentbl, pricetblN - they all have "productid" as the Primary KEY. there are less than 300,000 records in each table - when I ran the statement select productname from productdetailentbl left join producttbl on productdetailentbl.productid=producttbl.productid left join pricetblN on...
1
15972
by: Rudolf Bargholz | last post by:
Hi, We have created triggers to log modifications to tables in our application. The triggers work fine, just on one of the tables in our database the triggers fail with the error message <SQL0437W Performance of this complex query may be sub-optimal. Reason code: "1". SQLSTATE=01602>. The same trigger on other tables works fine (the triggers are autogenerated with the same structure for most of the tables used in our application). I...
0
8600
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9156
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8860
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6518
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5860
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4361
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4614
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3038
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
1998
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.