How many columns are in YOUR table?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

On average, how many columns are in your main MySQL tables?

Poll ended at Sat Feb 11, 2006 5:38 am

<10
6
33%
10-25
10
56%
25-50
1
6%
50-100
1
6%
100-500
0
No votes
>500
0
No votes
 
Total votes: 18

paladaxar
Forum Commoner
Posts: 85
Joined: Fri Jun 18, 2004 11:50 pm

How many columns are in YOUR table?

Post 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...
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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. "
paladaxar
Forum Commoner
Posts: 85
Joined: Fri Jun 18, 2004 11:50 pm

Post by paladaxar »

Do you really create tables with 1500 columns?!?
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
paladaxar
Forum Commoner
Posts: 85
Joined: Fri Jun 18, 2004 11:50 pm

Post by paladaxar »

so what IS a lot of columns in your opinion?
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

agreed with Pimp/Mark, I think the largest table I've had was 10 columns, maybe 15.. I normalize like hell. :)
Roja
Tutorials Group
Posts: 2692
Joined: Sun Jan 04, 2004 10:30 pm

Post 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.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post by Bill H »

I do have one table that has something like 35 columns in it, but the average is probably about 10.
RobertPaul
Forum Contributor
Posts: 122
Joined: Sun Sep 18, 2005 8:54 pm
Location: OCNY

Post 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.
paladaxar
Forum Commoner
Posts: 85
Joined: Fri Jun 18, 2004 11:50 pm

Post by paladaxar »

I'm sorry...but what is normalizing?
I'm somewhat of a newbie to this whole thing.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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)
BruceT
Forum Newbie
Posts: 14
Joined: Sat Aug 27, 2005 10:23 am

Post 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!
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post 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;
paladaxar
Forum Commoner
Posts: 85
Joined: Fri Jun 18, 2004 11:50 pm

Post 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.
Post Reply