Any DBA's in the house?

[Draciron Smith]

MySQL doesn't have a problem with that many fields in a table, however
it's usually a bad idea performance wise.

What you want to do is look at the data and see how it's typically
being used. You may want to normalize it down a little bit, especially
if some of the fields are extremely large.

Remember over normalizing is worse than under normalizing. I once saw
a supposedly proffesional DBA normalize Sex. He used a 6 char ID field
no less for a 1 char max value ROFL.

The way it works is every time you do a join, you make the DB create
temp tables. So when you over normalize especially on large record
sets you are gutting your performance unless you are reducing
redundancy. On the flip side pulling up data you are not using is a
performance hit. Redundancy will eat up amazing amounts of disk space
with large record sets as well.

For example if your normalizing projects. You might expect to have
fields like Proj name, Current Revision, Proj lead, people working on
the project, Liscence type, etc.

Proj Name obvously you normally won't normalize it. Revision though if
you have project turnover and want to maintain a list of who was
working on specific revisions you'll need to normalize that field.
Proj lead if you can have multiple proj leads you an either have
multiple proj lead fields or normalize that. People working on the
project should of course be normalized. Liscence type it depends. If
you are using abreviations better to just have the redundant data
since your only going to have a max of 5 chars. If you are exeeding 10
chars you normally want to normalize it.  With large recordsets on a
hard hit server you may want to denormalize something like that to
improve performance. If your cramped for space and performance is
great you may want to normalize with even as few as 5 or 6 chars.

As a rule you want to normalize all blob type fields which large
amounts of text. Those are typically drill down items anyway, pulling
up a million such fields will choke even the fastest servers.
Performance wise and practicality reasons it's usually better to just
use a Char feild than text or blob fields. You create a separate table
with the forign key(s) of the tables you want to use this with. Then a
sequence value so you an assemble the parts back together. It's a
little more work on the front end submitting the data as you have to
chop it up into 254 char blocks but it gives you almost infinite space
and none of the wierd bugs and limitations that come with using Blob
and text fields. The data is stored more efficently and it's far
easier to search char fields than text/blob fields.  A table of char
fields an be accessed by just about anything and every database driver
will not only handle them but also handle them in the same way. Blob
and Text fields are unsupported in many database drivers and are
handled differently by different drivers.

In my experience 15 to 30 fields are usually the largest tables I've
worked with. I've seen larger, seen 100 field tables composed almost
entirely of  forign keys. One relationship diagram we used an entire
wall to fit it on.Covered the entire thing top to bottom. You almost
had to use a magnifying glass to read the field names.  That's very
unusual however. Most of the time screen real estate is such that your
not displaying more than 10-15 values at a time anyway. Anything
that's a drill down item should be normalized unless it's a main field
for another view.

I know a kinda long winded way of saying that depends on x :)
Would be happy to look at it for you. Volenteered to do some DBA work
in the past but never got sent the schema, given access to the DB or
got a follow up so I assume somebody else wound up volenteering and
got picked instead.

On Mon, Jun 15, 2009 at 2:56 PM, Mike McGrath<mmcgrath at redhat.com> wrote:
>
> Is it better to have:
>
> id | smallString1 | smallString2 | smallString3 | largerString1
>
> or put the largerString1 in it's own table?  largerString1 I'm
> specifically thinking about is for http://bugzilla.redhat.com/503550
>
> Also, does mysql have a problem with having a table with many columns in
> it?  Where many== 20, 30, 40?
>
> I'm specifically talking about a table with around 1.2 million rows in it
> which is growing at a rate of several hundred thousand rows every 6
> months.
>
>        -Mike
>
> _______________________________________________
> Fedora-infrastructure-list mailing list
> Fedora-infrastructure-list at redhat.com
> https://www.redhat.com/mailman/listinfo/fedora-infrastructure-list
>