On Aug 7, 2:26 pm, Alpha Blue <[email protected]>
wrote:
> Matt Jones wrote:
> > Maybe not directly relevant, but have you considered that you may have
> > normalized your database into nonfunctionality? From the queries
> > you've shown, most of the tables have one data field(?!), a date, and
> > a foreign key field for the team. This would be an appropriate
> > structure for describing a system where each type of statistic might
> > be generated at different times, but it seems completely inappropriate
> > for your application where all the stats are (obviously) generated
> > simultaneously.
>
> > Why did you decide to go this way, vs a single 'stats' table for
> > everything?
>
> > --Matt Jones
>
> Hi Matt,
>
> There are approx. 37 different categories of statistics for NCAA
> football. Each category houses up to 14 columns. I would never attempt
> to build one table that housed 518 fields.
>
Why not? Even MySQL's fairly restrictive limits are more than 5 times
that (see http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html).
The general argument against that is that tables with that many rows
aren't correctly separating concerns. (see this post:
http://forums.digitalpoint.com/showpost.php?s=c6f4b98064317f2ad75a532f0e1268ee&p=8255322&postcount=11
for a prime example of doing it wrong)
However, in this case, a single event (a game) generates all 518
values.
> All of the stats are not generated simultaneously. They are built into
> separate weeks. Each statistics table houses 120 rows of data paired to
> the given week it was compiled. So, if there's say 17 weeks (counting
> bowls) for college football, then there will be 17 weeks worth of data
> (17 x 120 rows).
Nothing controversial there.
>
> Yes, I could simply place all of that into one giant table (1 stats
> table with 518 columns that contains 2040 rows per year. That's not
> going to happen. It's inefficient and difficult to manage. If the
> table gets corrupted, very likely given that type of scenario, it kills
> my entire site at once. You also have to account for 3 x /tmp space for
> the largest table you have in your database. Given the nature of this,
> and the fact that these stats will not be deleted for years, that can
> hit a server pretty hard.
>
Some quick math:
518 columns x 8 bytes per column (could be less, but assume there's
some overhead) -> 4144 bytes per row.
MySQL's limit is just shy of 65k, so no issue there.
2040 rows x 4144 bytes -> roughly 8.1 MB.
Given that there are live applications that manage hundreds if not
thousands of GBs of data in MySQL, I think this is a exceptionally
premature optimization, unless you plan on running the server
unaltered for the next 500 or so years. 3x /tmp space is similarly
beneath consideration, unless you're serving from an embedded
microcontroller or something.
Worrying about "table corruption" makes even less sense - if ANY data
gets corrupted, it's time to restore from backup.
Finally, I'll point out that while a combined row is way under the
limit, the mondo-JOIN query you're headed for is getting close to
another limit - the maximum 61 tables allowed in a join clause.
--Matt Jones
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Ruby
on Rails: Talk" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
