Page 1 of 2

How many columns are in YOUR table?

Posted: Thu Jan 12, 2006 5:38 am
by paladaxar
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...

Posted: Thu Jan 12, 2006 5:43 am
by JayBird
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. "

Posted: Thu Jan 12, 2006 5:49 am
by paladaxar
Do you really create tables with 1500 columns?!?

Posted: Thu Jan 12, 2006 6:27 am
by JayBird
paladaxar wrote:Do you really create tables with 1500 columns?!?
Nope, i dont...

If you start building databases with a lot of columns, then i believe there is a very good chance that the design of the database is all wrong and needs to be rethought

Posted: Thu Jan 12, 2006 7:03 am
by paladaxar
so what IS a lot of columns in your opinion?

Posted: Thu Jan 12, 2006 8:13 am
by JayBird
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

Posted: Thu Jan 12, 2006 8:23 am
by feyd
agreed with Pimp/Mark, I think the largest table I've had was 10 columns, maybe 15.. I normalize like hell. :)

Posted: Thu Jan 12, 2006 8:24 am
by Roja
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.

Posted: Thu Jan 12, 2006 9:37 am
by Bill H
I do have one table that has something like 35 columns in it, but the average is probably about 10.

Posted: Thu Jan 12, 2006 10:32 am
by RobertPaul
The biggest table in the DB I'm working on right now has 10 columns, with 3 of those being FK's ... it was 15 columns, before I normalized some stuff.

Posted: Thu Jan 12, 2006 10:43 am
by paladaxar
I'm sorry...but what is normalizing?
I'm somewhat of a newbie to this whole thing.

Posted: Thu Jan 12, 2006 10:56 am
by JayBird
paladaxar wrote:I'm sorry...but what is normalizing?
I'm somewhat of a newbie to this whole thing.
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

http://www.oreilly.de/catalog/javadtabp ... r/ch02.pdf
(yes, it is for java databases, but the principal is the same)

Posted: Thu Jan 12, 2006 12:14 pm
by BruceT
15-20 for most things, although like others have said, sometimes for special cases there are more. I think my largest was about 40-50 columns (a quick and dirty preference tracker, one column per check box or option). Normalizing is your friend!

Posted: Thu Jan 12, 2006 12:20 pm
by hawleyjr
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;

Posted: Thu Jan 12, 2006 12:28 pm
by paladaxar
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
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.