Page 1 of 1

Database engines - mixing them.

Posted: Tue Aug 07, 2007 2:26 am
by onion2k
Would it be bad practise to mix DB engines in the same database? For example, in some tables (a shopping cart for example) I'd rather like to have access to transactions so InnoDB is a logical choice. On the other hand, in other tables (my audit table that tracks what people do on the site for example) I'd like it to be speedy, not be held in memory and have access to delayed inserts so MyISAM is the obvious choice. But both in the same database? Is that asking for trouble?

Posted: Tue Aug 07, 2007 2:12 pm
by feyd
Provided your interaction is aware what features are available where.. no problem.

Re: Database engines - mixing them.

Posted: Wed Aug 08, 2007 12:10 pm
by ev0l
onion2k wrote: Is that asking for trouble?
Depends.

A problem can arise when mixing transactional and non-transactional tables. Data is not committed to a transactional table inside a transaction until you call commit on that transaction. Data is committed to non-transactional tables before the INSERT/UPDATE query return. If you insert/update both transactional and non-transactional tables inside a transaction and then you call rollback or your transaction fails there is a good chance that your non-transactional tables reference data that does not exist.

An example ...

Code: Select all

START TRANSACTION;
/*Transactional table with AUTO_INCREMENT field. */
INSERT INTO TransactionalTable SET var='value';

/*
** Your php code calls mysql_insert_id or you use other
** method to fetch the last AUTO_INCREMENT id
*/

/*Non-transactional table */ 
INSERT INTO NonTransactionalTable SET idFromTransactionalTable = 5;

COMMIT
/*
** WOOPS. Your commit fails but the non transactional table
** still references an id that does not refer to a row
** or refers to a row that was unintended
**  (due to the way MySQL deals with AUTO_INCREMENT it is most
**   likely the former )
*/ 
I think you would be better off using transactional tables especially with something like an audit table that receives frequent writes. In my experience InnoDB performs better than MYISAM on tables that more than %10 or %15 percent of the queries are writes. Remember MYISAM locks the entire table on INSERT/UPDATE.