Database engines - mixing them.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Database engines - mixing them.

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Provided your interaction is aware what features are available where.. no problem.
ev0l
Forum Commoner
Posts: 56
Joined: Thu Jun 21, 2007 1:50 pm

Re: Database engines - mixing them.

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