Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Page 1 of 4 1 2 3 ... LastLast
Results 1 to 10 of 31
  1. #1
    Junior Member
    Join Date
    Jul 2003
    Posts
    6
    Member #
    2152
    Hi there,

    Does anybody know what the size limitations are for an MySQL table? I want to create a table of about 400 fields, and 600 records, so it will be quite square. Most of these fields will be of type tinyint (four digits max), so I am thinking that storage should not be a major issue.

    Has anyone ever tried to use this many fields in MySQL before?

    Thanks in advance, j_col.

  2.  

  3. #2
    Senior Member filburt1's Avatar
    Join Date
    Jul 2002
    Location
    Maryland, US
    Posts
    11,774
    Member #
    3
    Liked
    21 times
    Apparently it can handle about 3400 columns (other restrictions). However, at that point your table will probably crawl so much that it'll be unusable. Consider splitting the columns into separate tables.

    Transio...isn't that what you're supposed to do? Not much of a database guru
    filburt1, Web Design Forums.net founder
    Site of the Month contest: submit your site or vote for the winner!

  4. #3
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Doesn't give the limitations on one ROW.

    Databases limit how much data can be stored in a single row, also.

    SQL Server is limited to something like 8192 bytes per row (excluding Text fields and Blobs).

    Not sure what MySQL is.

  5. #4
    Senior Member Brak's Avatar
    Join Date
    Apr 2003
    Location
    San Francisco, CA
    Posts
    3,413
    Member #
    1217
    Liked
    2 times
    I have a feeling you've got some bad database design in work... any table with 400 columns is a bad idea. Maybe explain what the purpose of all these columns are and maybe someone can offer a better alternative
    Kyle Neath: Rockstar extraordinare
    The blog | The poetry site | The Spore site

  6. #5
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    yeah, 400 columns is definitely bad design... anything over 20 columns needs some questioning.... unless you're building a reporting database.

  7. #6
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    Yes, that's a LOT of info. Using Normalization, that should be able to be cut down. For me, I have a 2x400 (guessing) table, and it's the only way to do it. that's huge IMHO.
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  8. #7
    Junior Member vornn's Avatar
    Join Date
    Jul 2003
    Posts
    15
    Member #
    2150
    The thing would be to look at the data and see if you really need all of that data, and if so, split it down into seperate tables. Here's some resources to help you:

    http://hotwired.lycos.com/webmonkey/...tutorial4.html

    http://www.keithjbrown.co.uk/vworks/...mysql_p7.shtml

    If you want some more help with your database, let me know.

    These should be of some help to you.

    :classic:

  9. #8
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Use MetaData to define each item. If they're the same datatype, they can occupy one column of a second table. Then create a "type" column which stores the MetaData that distinguish each element in the table.

    For Third Normal Form, you can then create a "types" table that identifies each type. This is called a Domain Table. Then use a foreign key to the "type_id" column where you had your "type" column.

    Anywhere you see repetitions in your data you should try to eliminate them.

  10. #9
    Junior Member vornn's Avatar
    Join Date
    Jul 2003
    Posts
    15
    Member #
    2150
    OK, found the stats for you:

    MySQL
    Columns in table 2599
    max table row length (without blobs) 65534
    table row length with nulls (without blobs) 65502
    query size 1048574

    Microsoft SQL Server
    Columns in table 1024
    max table row length (without blobs) 8036
    table row length with nulls (without blobs) 8036
    query size 16777216


    There is an entire list of info

    Here

    According to this, it should eat your table for breakfast!

    Hope this helps...

    :classic:

  11. #10
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Just a note to everyone who's thinking that MySQL is superior because it allows more data in one row... that is not a measure of superiority of the product, but rather a measure of the inferiority of people who use it


Page 1 of 4 1 2 3 ... LastLast

Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 04:07 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com