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;
2  5061 
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;
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; This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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...
|
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
|
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...
|
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
|
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
| | |
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
--...
|
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
|
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...
|
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...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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,...
| | |
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...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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...
|
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();...
|
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...
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
