At 10:12 AM -0500 11/12/06, Paul Houle wrote:
Paul:
Thanks very much for your most detailed answer, I'll pass it on.
I simply had a friend ask me and I didn't really know the answer, so
I asked this list.
I also should have asked, why so many columns ("fields" as us old
folk used to say)?
Cheers,
tedd
>tedd wrote:
>> Hi gang:
>>
>> I'm assuming that having 2000 fields in a table is not a problem, but
>> what is the maximum number of fields one can have in a table?
>>
>> For that matter, is there a list/reference showing the limits of MySQL?
>>
>> Thanks.
>>
>> tedd
>>
> There's supposed to be one in Appendix K of the Mysql manual, but I
>don't see a limit for the number of columns there. I suspect the number
>of columns in a table is different for different storage engines: for
>instance,
>
>http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.h
>
>says that an InnoDB table cannot have more than 1000 columns. Most
>databases (such as PostgreSQL, Oracle and Microsoft SQL Server) also
>have a limit on the total amount of data that can be in a row, although
>blobs are often stored differently... According to mysql manual,
>
>http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html
>
>"The sum of the lengths of the VARCHAR and CHAR columns in a table may
>be up to 64KB"
>
>-----
>
>However, there's a deeper question about why you want to put 2000
>columns in a table, and if that's really a good idea.
>
>I often end up working for people who start out with a filemaker
>database that grows out of control: often these people don't have any
>idea of the relational concept, so they end up building tables with
>hundreds of columns, rather than building separate tables that do a
>better job of representing the reality.
>
>For instance, imagine that an organization is keeping track of people,
>who have a relationship that changes over time. Imagine that a person
>can be in one of 30 statuses. So far so good. Now, say you want to
>keep track of the date that a person changed from one status to
>another. Quite often, filemaker users will create fields called
>'date1', 'date2', ... 'date30' to keep track of the dates.
>
>This always ends in tears.
>
>For one thing, it's impossible to represent the history of a person who
>ends up in a status twice. (Maybe they're an employee who goes on leave
>and then comes back.) The SQL coding for the above kind of system turns
>out to be painful; it's got the kind of regularity that's appealing to
>rookie programmers whose main tool is "cut-and-paste", but it's really
>unmaintainable when you need to change things in the future.
>
>Databases with huge column counts are often bad in the performance
>department. Sometimes you might just need 5 columns out of your 2000 --
>in a case like that, your database has to do 400 times more work than
>it really needs to.
>
>Tables with lots of columns are a "bad smell" that indicates a bad
>database design which could cause huge liabilities in the future. You
>really should be thinking about how to create a denornamlized database
>that has more tables with fewer columns... Here are some principles:
>
>* Never, never, never have columns with names like "something1",
>"something2", ... "something N"; instead create another table
>
>create table something (
> main_table_id integer not null,
> something_count integer not null,
> something_value varchar(255)
>)
>
>and join "something" against "main_table" when you're interested in the
>"somethings".
>
>* When you've got a lot of columns, usually some of them are optional.
>Maybe an e-commerce site has addresses for home, work, school, or
>maybe a digital library can represent many different types of things,
>such as books, audio recordings, video tapes, DVDs, unpublished
>manuscripts, etc. In a lot of cases, it makes sense to break out
>groups of optional fields (for instance, the ones relevant to video
>tapes) into their own tables, which are linked back to the main table.
>
>Years back I wrote a user management system
>(http://www.honeylocust.com/x/products/tum/) that had a number of
>columns that were regularly updated when users logged into the system.
>These were all fixed-length columns... It turns out that MyISAM has
>special optimizations for tables where all of the columns are fixed
>length. I got a big performance boost by putting the "hot" columns
>together in one table.
>_______________________________________________
>New York PHP Community MySQL SIG
>http://lists.nyphp.org/mailman/listinfo/mysql
>
>NYPHPCon 2006 Presentations Online
>http://www.nyphpcon.com
>
>Show Your Participation in New York PHP
>http://www.nyphp.org/show_participation.php
--
-------
http://sperling.com http://ancientstones.com http://earthstones.com
_______________________________________________
New York PHP Community MySQL SIG
http://lists.nyphp.org/mailman/listinfo/mysql
NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com
Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php
