List:General Discussion« Previous MessageNext Message »
From:John McCaskey Date:July 19 2004 9:49pm
Subject:RE: Relational Integrity
View as plain text  
MyISAM tables are sometimes faster than InnoDB, but for most
applications the difference is going to be negligible.  MyISAM tables
also use less disk space (more compressed row format).  These are the
only 2 advantages I'm aware of.  InnoDB on the other hand offers you
foreign keys and transaction support at a small speed penalty.  

The other difference between the 2 is that MyISAM does table level
locking, while InnoDB does row level locking.  This means that while
MyISAM is generally considered faster, it may actually turn out that
InnoDB is faster for you if you have a high level of concurrency
occuring and you end up with table lock contention between
processes/threads in MyISAM.

I switched to InnoDB about 8 months ago and have been very happy with it
ever since.  For me concurrency and table level locking where what
prompted the switch.  Foreign keys and transactions were just icing on
the cake.  For the application you describe I think you will do fine
with either table type.

John A. McCaskey

-----Original Message-----
From: Roy Harrell [mailto:1600uVision@stripped] 
Sent: Monday, July 19, 2004 2:03 PM
To: mysql@stripped
Subject: Relational Integrity


I need so general guidance on relational integrity.
I'm setting up a reasonably small DB with 30 or so
tables for a machine control application. Several of
the tables will have referential links to each other
(e.g. a finished part table will link to a master
part type table via the product ID number). 
None of my table will ever contain more than a few
hundred thousand records.

This database is a conversion from an existing MS SQL7 
system in which I made extensive use of foreign keys.
SQL7 has worked out well in the past but Windows and
VBNet has ceased to be an efficient machine control
development environment. We have decided
to migrate to Linux on all of our new systems where
practical.

My first stab at a MySQL implementation is to use the
MyISAM table structure and not the InnoDB structure,
foregoing the use of explicit foreign keys and letting
my apps take care of the relational integrity. I gathered
from reading DuBois that this is not an uncommon approach
to a MySQL implementation. Question: Are the advantages
of MyISAM tables vs. InnoDB tables sufficient for me
to continue this approach or am I better off setting
up InnoDB tables throughout?


Thanks in advance for any advice.
 
Sincerely,

Roy Harrell
Adaptive Equipment
2512 NE 1st Blvd #400
Gainesville, FL   32609
352.372.7821
1600uVision@stripped



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=1

Thread
Relational IntegrityRoy Harrell19 Jul
  • Re: Relational IntegrityMartijn Tonies20 Jul
    • Re: Relational IntegritySinger Wang20 Jul
RE: Relational IntegrityJohn McCaskey19 Jul
  • RE: Relational IntegrityLachlan Mulcahy20 Jul
    • RE: Relational IntegrityLachlan Mulcahy20 Jul