> 1) can you join a MyISAM table to an innodb table in a select? Absolutely.
> 2) Under 'Restrictions...' in the manual, it says: > When you restart the MySQL server, InnoDB may reuse an old > value for an > AUTO_INCREMENT column. > Under what circumstances does this occur? If you perform an INSERT into an InnoDB table w/ an AUTO_INCREMENT column, and for whatever reason the query fails (non-unique value in some unique column, or whatever), the AUTO_INCREMENT counter will still be incremented, despite the fact that the statement was rolled back. This happens even if you do an INSERT IGNORE, and the value that's been "eaten" will be returned by LAST_INSERT_ID() even though no row was actually inserted. Now, if I'm reading the documentation correctly, basically when you start MySQL the InnoDB engine does a "SELECT MAX(auto_inc_column) FROM ... FOR UPDATE", and uses that to initialize the in-memory counter. So if the last thing that happened on a particular table before MySQL was shut down was a bunch of failed INSERTs, you'll have received some values which pointed to nothing at the time, but will wind up being reused by InnoDB after the restart. > 3) Are there any replication issues? I have not encountered any InnoDB-specific replication issues. If anything, replication should be easier to deal with as you're guaranteed to not have half a transaction be visible on the slave if the link is severed... > 4) Any issues regarding a mixed MyISAM/InnoDB environment > that we should > worry about? Just be real careful to not accidentally count on a ROLLBACK undoing something in a MyISAM table... > 5) Anyone who has done this in the past -- how much of a pain was it? It's not a pain at all. There are things you need to be aware of that can bite you in the rear if you're looking for InnoDB's major benefits: Multi-versioned row-level locking is really really great. Finding out that "INSERT INTO x SELECT ... FROM y" still acquires a table lock on y is not so great. That's fixed in recent 4.0.x releases I think, but you get the idea. Deadlocks are also quite irritating, and need to be considered. In general, I tend to use InnoDB for pretty much everything. Assuming that you use transactions wisely InnoDB seems to be substantially faster for most operations than MyISAM (or at least, not noticably slower -- with a couple minor caveats, such as "SELECT COUNT(*) FROM x"), and it definitely scales better to large numbers of simultaneous users. Also, you get the benefits of FOREIGN KEY constraints and what not. There are a couple minor situations where InnoDB doesn't behave in a manner similar to MyISM: Having an AUTO_INCREMENT column as a secondary column in a PRIMARY KEY. If you do this in MyISAM, a separate counter value is maintained for each unique prefix, but this is NOT the case with InnoDB. InnoDB of course does not support FULLTEXT indexes, and so forth. -JF -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
