Re: How many columns??

[Miles Thompson]

At 06:32 AM 8/3/2006, Ratheesh K J wrote:


Hello all,

Just wanted to know how many columns are preferable in table. At present 
we are having nearly 50 - 60 columns in some of the tables. Is this ok or 
should we be splitting the tables for normalization.


If we really need to split then how better would it be in terms of 
performance.?


1) Splitting into related tables would then require Joins for data retrieval
2) Inserts/ updates will have to be done on more than one table now

It would be great to also know the best practices on number of rows that a 
table must hold.



Ratheesh Bhat K J



Read up on database normalization - there are benefits other than speed.

Retrieval is very fast when tables are appropriately indexed, so that's a 
non-issue. Similarly once a query is defined it's usually the conditions in 
the WHERE clause that change, so that is a non-issue as well.


With data normalized to third normal form, is you have to revise your 
structure or change the data you are storing, you will not break your 
application or your existing queries.


You may find, after normalizing your data, that you want to de-normalize 
part of it, at least you are doing so with full awareness of what it looks 
like in normal form.


As for number of rows - check the archives. There are tables with millions 
of rows, their number is generally not an issue. Remember that if a 
database grows and grows and grows, then suddenly performance hits the 
wall, you've likely reached the limits of the hardware it's running on, not 
the database.


Cheers - Miles


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.394 / Virus Database: 268.10.5/406 - Release Date: 8/2/2006



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

How many columns??

[Ratheesh K J]

Hello all,

Just wanted to know how many columns are preferable in table. At present we are 
having nearly 50 - 60 columns in some of the tables. Is this ok or should we be 
splitting the tables for normalization. 

If we really need to split then how better would it be in terms of performance.?

1) Splitting into related tables would then require Joins for data retrieval
2) Inserts/ updates will have to be done on more than one table now

It would be great to also know the best practices on number of rows that a 
table must hold.


Ratheesh Bhat K J

Re: How many columns does MySQL Support?

[Jan Steinman]

>From: "Andrew Kuebler" <[EMAIL PROTECTED]>
>
>2) Is it just as fast with Mysql to access 10 fields in a table with 10
>columns as it would be to access those same 10 fields among let's say 90
>other fields in a 100 column table?

Are you certain that performance should be your primary concern here?

I prefer to design databases with separate tables of tightly coupled information, then 
LEFT JOIN them as needed. This makes maintenance much easier, and may also be a 
performance plus, due to locality of reference issues.

Only when I'm certain that this form (4NF? I forget) is negatively impacting 
performance do I bother optimizing to 3NF by permanently joining tables.

 SQL SQL SQL SQL SQL SQL SQL SQL 

-- 
: Jan Steinman -- nature photography: 
: Bytesmiths -- artists' services: 
: Join the forums at 


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: How many columns does MySQL Support?

[Brent Baisley]

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


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

How many columns does MySQL Support?

[Andrew Kuebler]

Two questions:

1) How many columns does MySQL support per table?

2) Is it just as fast with Mysql to access 10 fields in a table with 10
columns as it would be to access those same 10 fields among let's say 90
other fields in a 100 column table? Would speed only be reduced if I
pulled a query on all 100 columns in the last mentioned table to only
then extract the 10 I needed locally within my program? I hope that
makes sense to someone out there and you understand what I'm trying to
say.. 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. I'm curious if it's
better overall to find a way to break-up the table into smaller separate
tables and keep track of which table contains the information I'm
looking for within my program, but I can see that being a 'bit of a
hassle..

Any help or input is appreciated. Thanks!
Best Regards,
Andrew Kuebler
AK Communications, Inc.
Phone: (866) 925-2666 x123 / Fax: 866-925-3296 (Toll-free)
http://www.akcomm.com



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php