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
