[Cialug] mysql database design

[Jon Clemons]

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
>