I'm not sure what the limit is. MySQL limits tend to be dictated by the
OS that is being used. The more columns you have the larger the database
file will be. You should try to avoid having many empty columns in a
database design, even if you are using varchar instead of char. Try to
think ahead in your design to what indexes you will create to speed up
searching. You obviously don't want to create 300 indexes (don't know if
you even can) and you want to avoid full database searches, which is
what happens with no indexes.
Going on a relevant tangent here. What could you need 300 columns for?
I'd be hard pressed to think of a dataset that would have 300 unique
pieces of data. Perhaps you can make your columns into records? A simple
example to clarify. I see many database designs that create maybe 10 or
more columns to store various phone numbers (home, work, cell, fax,
beeper, other, etc.). A better design would be to make them records in a
"phone number" database with a description field to indicate what phone
number it is. This gives you the ability to store unlimited phones
numbers and to easily search for a phone number (i.e. reverse lookup).
Plus, you can create one index to index all you phone numbers for quick
searching.
I would not split your data into separate tables. If you have to, create
a table with a column called "field name" where you can specify what
type of information a record holds.
On Tuesday, October 15, 2002, at 09:40 PM, Andrew Kuebler wrote:
> I have a table I need to build with about 300 columns and I'm
> concerned about performance issues, however, I would only extract from
> the table what I need, not all 300 at one time.
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
