The newest version of MySQL AB’s flagship open source database product includes new pluggable storage engines — swappable components that offer the ability to add or remove storage engines from a live MySQL server. In this interview, MySQL expert Mike Hillyer gives a deeper explanation of this new feature and explains how it can benefit DBAs.
I quite knew to MySQL so sorry if this turns out to be somewhat stupid ;P
From the article, there are different storeage engines good for different tasks. I know I can declare what type of engine I want when declaring a table, but is it possible to mix storage engines?
For example for tables that are mostly read MyISAM and for tables that are read/updated something else, better suited for the job.
damn, typing dissorder ;9
Yes, you can mix storage engines, so that you can have a single query referring to tables of different table-types.
At least you can with MyISAM and InnoDB – I have not used the other types.
There are limitations that come from using multiple engines. Indexing and foreign keys are somewhat optional in MySQL. Transactions are unpredictable when spread across multiple engines.
This could still be very cool. For example one of the engines actually uses remote tables. While I’m not really a fan of having table data all over the place in terms of physical storage, it is an interesting idea.
You can read more about it here.
http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html
Some things you may not know:
In MySql you don’t have to define a whole schema with a particular storage engine. If you have myISAM tables (tblsess) for session management with quick updates and reads, you can still have an archiving table (tblhistory) that uses the Archive storage engine and compresses data. You just define it as such in your create table statement:
Create table ‘tblhistory'(
history_id int(10) unassigned NOT NULL auto_increment,
history_desc…
) TYPE=ARCHIVE
Create table ‘tblsess'(
sess_id int(10) unassigned NOT NULL auto_increment,
sess_name…
) TYPE=MyISAM
Pluggable storage engines only change the way a table handles storing and retrieving data. As was previously mentioned, with FEDERATED tables you can actually can “link” to a table in a different database (like your MySql schema containing a link to an as/400 DB2 table), something previously not available. It’s similar to MS Access ODBC linking functionality. So in theory, instead of creating 2 or 3 database connections to different databases on different machines, you can create federated tables in mySql and query the table using mysql’s DML…pretty slick, I’d say.