Is it possible to make DB tables of different storage engines used?

Question

I'm creating a database schema that would contain tables (created from MyISAM and InnoDB) , could it be possible to mix tables like that? Can somebody help me please!..

Answer 1

It's possible, but the trade off is horrid in query speed, and failures are possible requiring converting the DB from MyISAM to InnoDB or vice versa to correct.

If you can just use MyISAM it will suffice, unless you're doing transactional work in a non-critical area. InnoDB is new, and still not as means tested in a true production environment (not good for a storefront, for example). It's row-locking and multi-sequential features are wonderful, yet DB crashing isn't fun, especially losing open queries.

MyISAM is used mainly for a reason -- it works in all environments. Yes, tables can lock, but forum admins have found a way to help on their poor overworked DB -- removing all unnecessary joins. The performance benefit is well worth the trouble of removing them, especially if your DB has a lot of transactional data processing. It also helps to prevent MyISAM table locks (for that alone it's a godsend).

Answer 2

Yes. Many RDBMSs support this, eg. MySQL.

EDIT: I LOVE SandyKIT's answer! InnoDB not used in production servers? The solution to MyISAM's drawbacks is to limit table joins? In a RELATIONAL database system? Classic! Hope your schemas are BCNF Sandy!

Oh, and I work for a Fortune1000 company that handles millions of lines of transactional data every day - on MySQL InnoDB tables. It's not unusual for some of our queries to have 10 or more joins in them. MyISAM doesn't even support transactions, let alone handle them better than InnoDB as Sandy suggests. MyISAM isn't even ACID compliant. InnoDB not means tested? It's been around nearly 15 years, and I've already given evidence of its successful use in a real-world, mission critical production environment (for several years), how much more "means-tested" would you want?

This "Sandy" is either a frightfully incompetent "server admin", or is flat out lying about their experience, because frankly she should know better.

Answer 3

It's perfectly supported by MySQL and it is designed to work like that. InnoDB has been around long enough to be used safely in critical applications. What you'd need to consider is the advantages of each storage engine that adapt the best to your needs.