1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Is there a limit to num of columns in MySQL?

Discussion in 'MySQL' started by onlyican.com, Jan 12, 2006.

  1. #1
    I am creating a massive website

    So far I have created 6 Pages and 5 MySQL Tables

    One table will be re properties
    With about 30 Columns

    Is there a "Safe" limit to the number of columns per table
     
    onlyican.com, Jan 12, 2006 IP
  2. arnek

    arnek Active Member

    Messages:
    134
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    53
    #2
    According to a survey done my MYSQL themselves,

    the official maximum limit for mySQL columns is 3398 columns per table.


    So I guess 30 columsn is realative acceptable - you can just go ahead, I've worked with massive tables on enterprise applications that create up to 100 columns...WoW
     
    arnek, Jan 12, 2006 IP
  3. onlyican.com

    onlyican.com Peon

    Messages:
    206
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #3
    I heard, if you have more than 10 columns, it is bad practive and you are not using your MySQL correctly
     
    onlyican.com, Jan 13, 2006 IP
  4. Brons

    Brons Peon

    Messages:
    55
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #4
    I agree, having that much columns in one table will only make your table more complex.

    Maybe you have a reason why you are doing this, but you might want to reconsider your database layout.
     
    Brons, Jan 13, 2006 IP
  5. Important

    Important Peon

    Messages:
    87
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Maybe you can setup multiple "relation tables". It's not advisory to stuff a table with too many columns.
     
    Important, Jan 22, 2006 IP
  6. mopotofu

    mopotofu Peon

    Messages:
    164
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #6
    I work primarily with Oracle databases, but the same principle applies to any other database. 30 columns isnt all that bad, however make sure your data is "normalized" properly - usually to avoid having data duplicated in the same table you have to set them up in different tables and definte the relationships among these tables.
     
    mopotofu, Jan 23, 2006 IP
  7. snlash

    snlash Peon

    Messages:
    5
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #7
    30 columns is messy, but doable. I don't really recommend it, but if there's no better way you can think of to do what you are doing....
     
    snlash, Jan 24, 2006 IP
  8. dfsweb

    dfsweb Active Member

    Messages:
    1,587
    Likes Received:
    55
    Best Answers:
    0
    Trophy Points:
    88
    #8
    IMO 30 columns is a bit messy but shouldn't create any problems. Just ensure that the database is a relational database to ensure that you are using the database efficiently.
     
    dfsweb, Feb 1, 2006 IP
  9. RectangleMan

    RectangleMan Notable Member

    Messages:
    2,825
    Likes Received:
    132
    Best Answers:
    0
    Trophy Points:
    210
    #9
    I have some of my DB's with 50 columns and 100 tables with about 200mb info in it. Not a problem. I sys admin a site with a 1.5gb Database on mysql and no problems. :) Mysql is pretty damn good just give it the ram it needs. We have more problems with apache than mysql.
     
    RectangleMan, Feb 11, 2006 IP
  10. prabhjyot

    prabhjyot Peon

    Messages:
    1
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #10
    I want to store a matrix of size 43000 x 43000 in a database. How do you suggest I store that?
    I could store it as 3 columns: (i, j, value) but that would mean that I have A LOT of rows! Would that be a problem?
     
    prabhjyot, May 5, 2008 IP
  11. YourChild

    YourChild Peon

    Messages:
    22
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #11
    I was looking around for some insight and saw this thread..any help would be appreciated...

    I'm implementing a 'buddy list' -IM messenger application for a website...its just like any usual buddy list you see ie aim, yahoo, msn...

    the maximum number of buddies one can add to their buddy list is 500

    and so far, my implementation for the buddy list table looks like this:

    
    
    userID  |  friendID_1  | friendID_2  | friendID_3 ..... | friendID_500
    ---------------------------------------------------------------------
    1           5               9            13                 200
    
    
    Code (markup):
    So each row contains 501 columns, 500 slots opened for buddies and 1 for the user's ID (the friendID_i column remains empty and gets filled up as the user adds buddies to their buddy list)

    After reading this thread, I realize that 500 columns per row is probably going to look really ugly and slow down performance by a lot...

    Whats a better way to go about implementing this? I just want to be able to keep track of which buddy belongs to which user.
     
    YourChild, Jun 23, 2008 IP
  12. anole

    anole Peon

    Messages:
    1
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #12
    In response to YourChild's post:

    You need to create a join table. To make things as efficient as possible, you should create a many-to-many join table. Your structure would look like this:

    user table: userID, userName, etc. etc.
    friend table: friendID, friendName, etc. etc.
    userFriend table: userID, friendID

    In this way, many users can have the same friend, but you only have to create the friend once. After that, you are just linking users to friends.

    This will save database space and make the tables easier to read as well, since a person with 500 friends will have 500 rows, whereas a person with only 2 friends will only have 2 rows!
     
    anole, Jul 11, 2008 IP
  13. webstoreinnovations

    webstoreinnovations Peon

    Messages:
    1
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #13
    I don't really see any reason for limiting the number of columns in a table - it all depends on the application and the GUI editor you use for adminstering mysql and whether you enjoy scrolling through loads of columns.

    If you're really worried about it just create linked tables that use a foreign key (with cascades on update and delete) on the primary key linking back to the original table's primary key.
     
    webstoreinnovations, Nov 30, 2011 IP
  14. Rukbat

    Rukbat Well-Known Member

    Messages:
    2,908
    Likes Received:
    37
    Best Answers:
    51
    Trophy Points:
    125
    #14
    That's because there isn't any. As long as the database is designed properly, worrying about the number of columns is like worrying about the number of periods printed on the newspaper in the back seat of your car. We have what we call a "255 record" - it's a standard record type that we use for most of our customers' data, and it has 255 fields (columns). Considering that we have tens of thousands of these tables, some with many millions of records each, you'd hear the crash on Mars if there were a problem with wide tables. (We have wider tables [there's effectively no limit to columns or rows with the database we use]- that's our most common one.)

    The limit is the database program itself. If the database can handle 3398 fields, you can feel safe designing a table with 3398 fields. (Assuming you trust the database vendor.)
     
    Rukbat, Dec 1, 2011 IP
  15. razstec

    razstec Greenhorn

    Messages:
    1
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    21
    #15
    Hi all i know this is an old post but i a db with 15 tables with 10 or less collumns but now i have to create a table with 89 int fields and another 89 varchar(50) fields, what do you advice? Split the int field in one table and varchar in another? iven thou it would be 89 collumns.
    Between im using mysql
    thanks
     
    razstec, Apr 27, 2015 IP
  16. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #16
    Put them in 1 table. Whatever 10 column rule they are referring to is ridiculous and completely irrational.

    I have some financial spreadsheets with hundreds of columns. There's no logical method of splitting up a single record containing 500 columns, not to mention it makes reporting and manipulating data almost impossible if it is segmented between tables.

    Unless there is a logical reason you would split the table, or you are trying to store data in a denormalized manner (such as storing a csv list as a single value), use as many columns of data as you need for a single record.
     
    jestep, May 8, 2015 IP
  17. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #17
    PoPSiCLe, May 8, 2015 IP