How many columns are in YOUR table?
Moderator: General Moderators
How many columns are in YOUR table?
While I was creating a table the other day, I created a lot of columns...well...what I thought was a lot. My table has about 18 columns. While creating the table, I started to wonder: How many columns would it take for a table to qualify as "larger than normal"? Will 10 columns start to bog down a db? Or maybe 100? Or can MySQL handle 1,000,000 columns with ease?
I'm curious to see how big other PHPers db tables are. Maybe my 18 columns makes my table huge...maybe it's tiny in comparison to others. We'll see...
I'm curious to see how big other PHPers db tables are. Maybe my 18 columns makes my table huge...maybe it's tiny in comparison to others. We'll see...
I think the maximum number of columns in a MySQL table is 3398
and a quote from a user in the MySQL doc
"The length of column names seems to play a part in the maximum number of columns you can have in a table. For version 4.1.14, I can have a table with 2410 columns when the column names are pretty short -- say, "col1... col2410". If the column names are longer (more descriptive), say, "maxPftAmtInYr" and so on, the number of columns seems to drop to around 1500 - 1700! Don't know why or what this is but it sure is a pain... but the only one I've had with MySQL in over 6 years of use. "
and a quote from a user in the MySQL doc
"The length of column names seems to play a part in the maximum number of columns you can have in a table. For version 4.1.14, I can have a table with 2410 columns when the column names are pretty short -- say, "col1... col2410". If the column names are longer (more descriptive), say, "maxPftAmtInYr" and so on, the number of columns seems to drop to around 1500 - 1700! Don't know why or what this is but it sure is a pain... but the only one I've had with MySQL in over 6 years of use. "
Persoally, if i started get to around the 20 columns mark, id be questioning my DB design.
There are many cases where you may actually need this many, but it is always better to question yourself and look at the design from another angle.
I have done tables with more than 20 columns when there serioulsy was a need, but those occasions (for me anyway) are very few and far between.
Mark
There are many cases where you may actually need this many, but it is always better to question yourself and look at the design from another angle.
I have done tables with more than 20 columns when there serioulsy was a need, but those occasions (for me anyway) are very few and far between.
Mark
The largest I have in any of my current projects is 33. It needs to be split, but the surrounding code that has to be updated to do so is enourmous (10,000+ lines). After splitting, it will be 25, and that to me is rather large.. its pretty close to the point where I think the design would have to be revamped. I don't see any simple redesign (beyond that split) that would retain both the relationship of the data, and a realistic ability to do selects.
Based on that, I chose 10-25.
There are certainly situations that would break that mold, but thats my rule of thumb.
Based on that, I chose 10-25.
There are certainly situations that would break that mold, but thats my rule of thumb.
-
RobertPaul
- Forum Contributor
- Posts: 122
- Joined: Sun Sep 18, 2005 8:54 pm
- Location: OCNY
Read the normalization chatper in this PDF book - starts at page 35, but you may wanna read the whole lot to get a better grasp of DB designpaladaxar wrote:I'm sorry...but what is normalizing?
I'm somewhat of a newbie to this whole thing.
http://www.oreilly.de/catalog/javadtabp ... r/ch02.pdf
(yes, it is for java databases, but the principal is the same)
127 - I have a 3 - 6mb flat file that I have to import every day for a MLS site. I could normalize this file if I wanted to (Especially the Features part) However, This is much easier to maintain.
Code: Select all
CREATE TABLE `tbl_mls_listings_main` (
`listnum` int(11) NOT NULL default '0',
`listOffCode` int(11) default NULL,
`listAgent` int(11) default NULL,
`statusCode` text,
`catgNum` int(11) default NULL,
`streetName` text,
`streetDir` text,
`streetNum` text,
`listDate` text,
`latitude` text,
`longitude` text,
`city` text,
`listPrice` int(11) default NULL,
`rentalAmt` int(11) default NULL,
`photo_flag` text,
`bedrooms` int(11) default NULL,
`proptype` text,
`areacode` int(11) default NULL,
`common_subd` text,
`county` text,
`zoning` text,
`email_addr` text,
`elem_school` text,
`high_school` text,
`homestead` int(11) default NULL,
`legal_subd` text,
`land_use_code` text,
`middle_school` text,
`full_baths` int(11) default NULL,
`half_baths` int(11) default NULL,
`region` int(11) default NULL,
`state` text,
`tot_sqft` text,
`waterfront_yn` text,
`zipcode` text,
`builder` text,
`new_construct` int(11) default NULL,
`officename` text,
`officephone` text,
`agentname` text,
`agentphone` text,
`zoning_a` text,
`zoning_b` text,
`zoning_c` text,
`ipix` text,
`features1` text,
`features2` text,
`features3` text,
`features4` text,
`features5` text,
`features6` text,
`features7` text,
`features8` text,
`features9` text,
`features10` text,
`features11` text,
`features12` text,
`features13` text,
`features14` text,
`features15` text,
`features16` text,
`features17` text,
`features18` text,
`features19` text,
`features20` text,
`features21` text,
`features22` text,
`features23` text,
`features24` text,
`features25` text,
`features26` text,
`features27` text,
`features28` text,
`features29` text,
`features30` text,
`features31` text,
`features32` text,
`features33` text,
`features34` text,
`features35` text,
`features36` text,
`sect` int(11) default NULL,
`range` int(11) default NULL,
`township` text,
`tot_acres` text,
`bed1_wid` text,
`bed1_lf` text,
`bed1_len` text,
`bed2_wid` text,
`bed2_lf` text,
`bed2_len` text,
`bed3_wid` text,
`bed3_lf` text,
`bed3_len` text,
`bed4_wid` text,
`bed4_lf` text,
`bed4_len` text,
`bed5_wid` text,
`bed5_lf` text,
`bed5_len` text,
`bonus_wid` text,
`bonus_lf` text,
`bonus_len` text,
`break_wid` text,
`break_lf` text,
`break_len` text,
`dining_wid` text,
`dining_lf` text,
`dining_len` text,
`family_wid` text,
`family_lf` text,
`family_len` text,
`great_wid` text,
`great_lf` text,
`great_len` text,
`kitchen_wid` text,
`kitchen_lf` text,
`kitchen_len` text,
`kitchen_wid2` text,
`kitchen_lf2` text,
`kitchen_len2` text,
`living_wid` text,
`living_lf` text,
`living_len` text,
`taxid` text,
`lot_size` text,
`photocount` int(11) default NULL,
PRIMARY KEY (`listnum`)
) TYPE=MyISAM;wow...thank you. I will be sure to read through that thouroughly. I have been looking for a good explanation of how to set up db's. Until this point, I have just been kind of guessing...reinventing the wheel I guess you could say. Thanks again.Read the normalization chatper in this PDF book - starts at page 35, but you may wanna read the whole lot to get a better grasp of DB design