Re: [PHP] Mysql/php database performance question

[Khalid El-Kary]

Oh, sorry, i should have remarked that all of the columns should be fixed width (CHAR) for this optimization to take effect, since if even one of the columns is variable width (VARCHAR) the whole row will be variable width, and it will be no use to change to CHAR.

Regards,
Khalid

seems to be a little bit more complicated:

CREATE TABLE `aa` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`aaa` CHAR( 50 ) NOT NULL ,
`bbb` CHAR( 50 ) NOT NULL
);

both aaa and bbb are char now


CREATE TABLE `aaa` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`aaa` CHAR( 50 ) NOT NULL ,
`bbb` VARCHAR( 255 ) NOT NULL
);

aaa will be varchar anyway

CREATE TABLE `aaa` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`aaa` CHAR( 50 ) NOT NULL , );

aaa is char

ALTER TABLE `aaa` ADD `bbb` VARCHAR( 250 ) NOT NULL ;

aaa in now VARCHAR

Seems like one cannot mix char and varchar columns in one table

Matt Schroebel wrote:

-----Original Message-----
From: Marek Kilimajer [mailto:[EMAIL PROTECTED]] Sent: Friday, January 10, 2003 11:45 AM
To: Matt Schroebel
Cc: Simon Dedeyne; [EMAIL PROTECTED]
Subject: Re: [PHP] Mysql/php database performance question


Sure, just tried it (32-bit platform, might be >7 for 64-bits).
I have a feeling it is somewhere in the manual.

How'd you try it?

I created a 1 column 42 char record in phpMyAdmin.  Everytime I add a
row, regardless of size the dataspace increases by 42.

With a second table, with 1 column varchar(42), each 4-5 char insert
resulted in 20 bytes of space (must be some minimum overhead), and a
full 42 resulted in 44 bytes of dataspace used.

I'm curious here, as it seems the trade off is speed of access with char
[and the overhead of removing trailing spaces on each retrieval] vs
storage size in varchar [and it's improved strip right spaces on storage
only happening once].  That's what the man page I pointed to last time
said.  There are some examples of truncating data to 4 bytes on that
page but no mention of storing char as varchar.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

_________________________________________________________________
MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php