Re: table type and server load

Responses intermingled ---

--- Joelle Tegwen <[email protected]> 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?

> 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?

> 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.


> 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.

> 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.


> Thanks
> Joelle
> 

No problem,

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

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