[prog] mysql question

rachel rachel at xtreme.com
Mon Feb 9 23:05:08 EST 2004


wolf wrote:
> Hi, I was wondering if anyone would happen to know what the
> differences in performance, compatibility, and functionality
> are between using MyISAM and INNODB in MySQL?

Well, since nobody more expert has jumped into this yet, I will.  I
assume you've read or are reading the MySQL documentation.  It's true
there isn't anything that I could find at least that gives a
point-by-point comparison.

There are two major functional differences between MyISAM and InnoDB
table types.  InnoDB can handle transactions, while MyISAM does not, and
InnoDB enforces foreign key constraints and MyISAM does not.  They are
syntactically compatible - you may use the same SQL, and MyISAM types
will simply ignore unsupported commands.

For example, let's say I have a table called CUSTOMER and a table called
ORDER.  Now, I don't want any orders to go through that aren't
associated with a customer, so I might do:

ALTER TABLE ORDER
    ADD CONSTRAINT ORDER_CUSTOMER FOREIGN KEY (CUSTOMER_ID)
    REFERENCES CUSTOMER(CUSTOMER_ID);

Now, if both ORDER and CUSTOMER are InnoDB tables, you will get an error
if you attempt to insert an order with a CUSTOMER_ID that does not
appear in the CUSTOMER table.  Also, if you try to delete a customer who
has existing orders, you will get an error.  With MyISAM tables you may
do either of these things and the db won't make a peep.

Regarding transactions, I've not been able to think of a GOOD simple
example, so here's a bad one, but it should give the general idea.

We have another table, ITEM, which tracks our inventory, and we have
some orders to add to the ORDER table.  We might do this:

BEGIN;
INSERT INTO ORDER (ORDER_ID, ITEM_ID, QTY, CUSTOMER_ID)
     VALUES (1, 1234, 4, 2);
SELECT QTY FROM ITEM WHERE ITEM_ID = 1234;

-- our code checks here for whether there are 4 or more items in
-- inventory; if yes, we'll go on:
UPDATE ITEM SET QTY = QTY - 4 WHERE ITEM_ID = 1234;
COMMIT;

-- if there were fewer than 4 items, instead we'll do
ROLLBACK;

And notify the customer that we can't place the order.  (Please note
that nobody sane would write it this way, it's just an example!)

Basically a transaction guarantees that either ALL the included
statements are executed (COMMIT), or none of them are (ROLLBACK).  A
MyISAM table would have done the insert into the ORDER table when it was
called, selected the QTY, and been unable to reverse out the ORDER if
there weren't enough ITEMs in stock.

There are other differences that I'm not as familiar with, but those are
the major ones.  I've never done any benchmarks so I can't speak to
performance detail, but I feel quite sure that InnoDB tables will be
slower, due to the integrity checks they need to do, but how much slower
I can't say.  It would depend also on what checks you've asked for.
Many people consider this to be a worthwhile tradeoff.

HTH!

Rachel


> 
> Thanks!
> 
> _______________________________________________
> Programming mailing list
> Programming at linuxchix.org
> http://mailman.linuxchix.org/mailman/listinfo/programming
> 
> 





More information about the Programming mailing list