Re: Mysql/php database performance question

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:kilimajer@webglobe.sk] Sent: Friday, January 10, 2003 11:45 AM To: Matt Schroebel Cc: Simon Dedeyne; php-general@lists.php.net 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