Kevin McCarthy wrote:
> Cool, thanks for all the great information! I'm reading up on
> normalization - there's so much to learn about databases.
>
> == Quote from lark's article
>>from the perspective of mysql performance, the performance goes down as the number
>> of columns increases! best practice is to divide the data and load them in several
>> different but somewhat meaningful tables.
>
> Just to be clear, you are saying that this would only improve
> performance in the case that my queries do not ask for all the fields
> at once, right? Or would dividing my tables in this manner improve
> performance even though I JOIN all of them every time I query them?
>
>
>
> A little more information about my database:
>
> 1. Luckily, ALL of the data is either a float or an integer.
> 2. Each row represents a city. Cities are in a different table, but I
> join them together with their census data based on an integer key.
> 3. Requests will come from a script that will mostly do the same query
> every time. Most of the time, I will want all of the fields, so if
> it's all in one big table, it will probably be a SELECT * FROM ...
> WHERE city_id=
>
> If I understand you guys correctly, there are only two reasons why I
> would want to split my table:
>
> 1. I don't need every field every time - split the table into subsets
> based on usage, like if I only needed the first 10 fields most of the
> time, and all of the fields only some of the time.
> 2. The fields have different data types - keep all the of the
> different data types together in different tables
>
> I might actually end up using this same database for a couple of
> different applications, one of which would not require all the data
> every time, in which case I might end up splitting the tables for
> reason #1.
>
> Thanks again for your great responses!
>
> Kevin
>

from what you have described above, i think you now have a better
understanding of the issues.

to answer your question above about the performance, the performance of
your tables increases regardless of whether queries ask for all fields
at once or not! likewise, if tables are somewhat logically divided and
properly indexed, the performance of your joins will increase regardless
of how many columns/rows you return.


hope this helps.