Re: table type and server load

... some snipping and more mingling ...

--- Joelle Tegwen <[email protected]> wrote:

> more intermingling...
> 
> Shawn Green wrote:
<snip>
> >
> > 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?

You need them only if you want the database to verify that the values
in certain columns already exist in other tables (eg: verify that the
lookup_id of one record is actually a valid value because it really
does exist in the lookup table). The way you describe your situation,
you can probably do that validation in your code for as few records
that you may need to add or change.
<snip>
> > 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?

Maybe the details are beyond the scope but the theory isn't. Some good
reasons to put data common to several sets of other data in it's own
container (database):

a) security - you can grant read-only permissions to most of the data
access accounts to your lookup tables. That way you don't possibly get
five dozen people trying to muck with the most important lists.
Contrary to what several managers might think: Access Control is a good
thing.

b) organization - If it feels "natural" to organize the data in a
hub-and-spoke pattern, do it. It's a form of normalization to eliminate
duplicate tables of information in separate databases. Combining them
into a centralized database is logical under many circumstances.

c) memory optimization - you could preload that database into memory
(or operate that database off of a RAM disk, if you have the space).
Because so many other queries need to get data from this one central
location, you can get some serious throughput boosts by improving the
storage of these central facts.

There are probably a dozen other reasons to split or not to split your
data but those are the ones I could think of quickly.

<snip>
> 
> Thanks much!
> 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]