Re: table type and server load

more intermingling...

Shawn Green wrote:
>> I'm currently working on an application that looks like it's going to
>>
>> have (we're still finalizing the database design) 12 lookup tables 
>> (tables of mostly static data) and 7 high entry data tables (very 
>> denormalized). However, relative to other applications, the whole
>> thing 
>> is pretty static. 
>>     
>
> If they are relatively static, how often are they upated? Hourly?
> Daily? Weekly?
>   
The "data entry" tables will get new information every year (or maybe 2 
years). Each time a new set of data is entered it's a new "reporting 
cycle"  The data is all entered, verified, (updated etc) and then when 
it's finalized the new information all rolls out to be "public" and 
reportable. In theory, once it's finalized that's it, but I'm told that 
is not always reality. It looks like the people we are reporting data on 
have about a month to dispute information post "finalization" The lookup 
tables might have information added to them each reporting cycle, but 
it's on the scale of 0-10 additions vs 10,000 in the data entry tables.
>> It's a reporting application and reporting happens
>> no 
>> more than once a year (maybe once every 2 years).
>>
>> Over the next 5 years we intend to add on to this application 3 other
>>
>> reporting groups that will (for arguments sake) add 5 lookup tables
>> and 
>> 7 data tables each. (I really don't know I'm just kind of guessing 
>> here). Many of the original 12 lookup tables will be reused in the
>> other 
>> reports.
>>
>> To some extent the reports would be accessed separately, but
>> eventually 
>> they want to be able to see the data from the different reports
>> combined.
>>
>>     
>
> I thought you were storing data and building the reports from the data.
> Are you just storing the results of an existing report from some other
> system?
>
>   
We are storing data and building reports from them. Right now there's 4 
reports that are on similar topics but the data is presented in a very 
silo-ized kind of way. Eventually we hope to be able to integrate the 
data. I can go into more detail with examples if it's still not clear.
>> So I have a few questions.
>> 1) The data is very relationtional, but would you use MyISAM anyway 
>> because it's such a read heavy application?
>>
>>     
>
> How relational a set of data is shouldn't affect your choice of which
> engine (unless you NEED foreign key constraints. I that case you must
> use InnoDB).  MyISAM is the fastest engine for mostly-read type usage.
>   
How do I know if I NEED foreign key constraints?
>   
>> 2) How does the use of federated tables affect table access
>> efficiency? 
>>     
>
> Federating a table requires an extra relay of your query from one
> server to another, spooling the results on the server to which you
> originally posted the query, then transmittal of those results to you.
> What you may be able to gain in performance (one report per federating
> server?) you have to balance against the networking overhead involved
> in distributing the query. Depending on the setup and the query traffic
> you will have, FEDERAT-ing a table can either help or hurt your
> performance. 
>
>   
>> i.e. If I put the "data entry" tables each in their own schemas and
>> the 
>> lookup tables in another and used federated tables to connect them
>> does 
>> that improve the lookup speed? Or does it really not matter since
>> it's 
>> mostly lookup anyway?
>>
>>     
>
> Schemas are generally all on the same server. You do not need to
> federate tables in order to perform cross-database queries. You only
> need FEDERATED tables if you want to do cross-server queries.
>   
Sorry, I'm still stuck in Access mode I guess :) I get it now.

So separating out the lookup tables from the data  tables is just an 
architectural choice. How do I decide whether or not to separate them or 
make one big schema for the whole thing (or something else entirely)? 
Maybe this is beyond the scope of this list?
>   
>> 3) Are there other ways to make this more efficient?
>>     
>
> Creating indexes of the appropriate types on the appropriate columns
> will always help. If you are worried about space (as this seems to be
> more of a reporting archive application) you may consider using the
> ARCHIVE engine.
>
>   
Maybe after the data is "finally final" we could use ARCHIVE.I was 
definitely considering it for the data from previous reporting cycles.

Thanks much!
Joelle
>   

-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/[email protected]