[Cialug] mysql database design

[Dave J. Hala Jr.]

I've done that in the past. So far, I found that it is  generally a slow
process.


On Thu, 2007-08-09 at 11:26 -0500, Jon Clemons wrote:
> Is creating a temp table and inserting the data there and reading it
> then dropping the temp table doable. Maybe a few selects or a union
> with a temp table would do what you need.
> 
> 
> 
> 
> ----- Original Message ----- 
> From: "Dave J. Hala Jr." <dave at 58ghz.net>
> To: "Central Iowa Linux Users Group" <cialug at cialug.org>
> Sent: Thursday, August 09, 2007 10:56 AM
> Subject: Re: [Cialug] mysql database design
> 
> 
> >I really don't have to join *all* of them. I organized (grouped) the
> > data and setup the tables in a way that made sense. I could do a couple
> > of separate queries, but I was wondering how others dealt with this
> > issue.
> >
> > Anyway, its a really big a report with about 50 separate data points,
> > and in this case, most of them column sums.
> >
> > :) Dave
> > On Thu, 2007-08-09 at 10:37 -0500, David Champion wrote:
> >> It's not that bad, if you write your joins logically, using explicit
> >> JOIN instead of just joining in the WHERE.
> >>
> >> I'm just curious as to how you've come up with a situation where you
> >> _have_ to join 10 tables.
> >>
> >> -dc
> >>
> >> Dave J. Hala Jr. wrote:
> >> > I'm gonna give the massive join a try here sometime in the next couple
> >> > of days. However, thinking about and running a sql statement for a
> >> > 8-9-10 table join just makes my butt cheeks clench. We'll see how it
> >> > goes.
> >> >
> >> > :) Dave
> >> >
> >> > On Thu, 2007-08-09 at 09:57 -0500, Morris Dovey wrote:
> >> >> Dave J. Hala Jr. wrote:
> >> >> | The primary key is an integer.
> >> >> |
> >> >> |
> >> >> | On Thu, 2007-08-09 at 09:33 -0500, Morris Dovey wrote:
> >> >> || Dave J. Hala Jr. wrote:
> >> >> ||| I'm working on a database design for storing and retrieving
> >> >> ||| demographic information. The server is Mysql 4.x
> >> >> |||
> >> >> ||| Basically, I have a "root" table with a primary key and ten tables
> >> >> ||| with records (10,000 of them) that are related to the root table
> >> >> ||| via the primary key.  Inserting the data is at the moment a
> >> >> ||| non-issue. However, retrieval is. Generally speaking I do not do
> >> >> ||| joins across more then 3 tables.  In this instance it could be
> >> >> ||| helpful to do a join across more than 3.
> >> >> |||
> >> >> ||| My concern is performance. Has anyone been in this situation
> >> >> ||| before? How did your database design get around this issue?
> >> >>
> >> >> In a couple of the systems I've worked on we implemented trie indexes
> >> >> for _extremely_ fast retrieval. In one case the trie was used in
> >> >> parallel with the indexing provided in a canned db package, and in the
> >> >> others it was the only index used.
> >> >>
> >> >> If you're not familiar with the trie, you can probably find good info
> >> >> via Google. It provides very fast retrieval, but only so-so insertion
> >> >> speed.
> >> >>
> >> >> Morris Dovey
> >> >> DeSoto Solar
> >> >> DeSoto, Iowa USA
> >> >> http://www.iedu.com/DeSoto/
> >> >>
> >> >> _______________________________________________
> >> >> Cialug mailing list
> >> >> Cialug at cialug.org
> >> >> http://cialug.org/mailman/listinfo/cialug
> >> >
> >> > _______________________________________________
> >> > Cialug mailing list
> >> > Cialug at cialug.org
> >> > http://cialug.org/mailman/listinfo/cialug
> >> >
> >>
> >>
> >> _______________________________________________
> >> Cialug mailing list
> >> Cialug at cialug.org
> >> http://cialug.org/mailman/listinfo/cialug
> >
> > _______________________________________________
> > Cialug mailing list
> > Cialug at cialug.org
> > http://cialug.org/mailman/listinfo/cialug
> > 
> 
> _______________________________________________
> Cialug mailing list
> Cialug at cialug.org
> http://cialug.org/mailman/listinfo/cialug