List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:October 16 2002 1:13pm
Subject:Re: How many columns does MySQL Support?
View as plain text  
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

Thread
How many columns does MySQL Support?Andrew Kuebler16 Oct
  • Re: How many columns does MySQL Support?Brent Baisley16 Oct
Re: How many columns does MySQL Support?Jan Steinman17 Oct