I am almost willing to bet that a 2000 field DB can be cut to no more
then 100 fields __ just a sixth sense.
It's like one of those commandments in programming, if your code gets
to be more then a page or so for a .php file, think again. Yes,
includes are your friend. And so are classes and DEFINES.
:-) ed
On 11/12/06, tedd <[EMAIL PROTECTED]> wrote:
> 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
>
--
the Blog: http://www.utopiaparkway.com
the Karma: http://www.coderswithconscience.com
the Projects: http://flickr.com/photos/[EMAIL PROTECTED]/
the Store: http://astore.amazon.com/httpwwwutopic-20
_______________________________________________
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
