Where does real power of MySQL Storage Engines, and pluggable storage engines as MySQL 5.1 lays ? It is very much advertised this allows third parties to create their own storage engines and we can see solutions as Solid and PBXT . Plus there is Falcon storage engine being developed inside MySQL.
All of these storage engines are however similar in their features and focus - all of them are transactional storage engines with multi versioning and row level locks so it is question of architecture implementation details of how it would compare to Innodb storage engine - old timer in this space. Does it add choice to the people ? Yes! but it also adds high level of complexity because it will not be easy to figure out which storage engine or storage engine mix is best match for you. Believe me people even often have hard time figuring out between MyISAM and Innodb and if they do they use some generic (and very wrong) ideas such as “MyISAM is always faster for reads”.
The main benefit I see from these general purpose storage engine is they apply pressure on each other and as books says competition leads to customer winning. When Falcon looses to Innodb in certain cases it makes Jim to try harder, if Heikki sees Innodb does not scale as well as PBXT it will be an extra pressure to do the fixes. While Innodb was only transactional storage engine there surely was less pressure - if you’re migrating from Oracle to MySQL you of course may consider to delay migration but if you’re already with MySQL and requiring transactions you’re stuck with Innodb if you you’re happy or not. (This is not to say Innodb is bad storage engine, but we all know nothing is perfect and has its problems).
The great Innovation I think happens with custom storage engines which are not designed to be run as core storage engines and are not match of functions which you’d find in Oracle, PostgreSQL and other databases. MEMORY storage engine is old timer of this type, ARCHIVE, CSV are other good examples. There are also storage engines out where to read sensor data, talk to memcache or external search engine . As MySQL 5.1 and plugable storage engine interface matures I hope we’ll see more storage engines which are great serving one particular talk but doing it in simple efficient and innovative way. NDB cluster is yet another example I’d put in here. I do not feel it is ready as main storage engine for most of applications just yet but for some cases it may be handy.
The fact which is limiting innovation at this point is the fact MySQL has only Storage Engine pluggable but SQL layer is kind of fixed same for all. This dramatically limits what you can do. Such limit is not a big surprise though as execution layer is much more complicated is constantly changed and different optimizations may change internal code paths dramatically.
I know number of projects being interested in special techniques applying to optimizes certain types of queries with MySQL, it is not easy to do however as there is no interface. We had the same problem with Sphinx - Sphinx can run sorting and group by operations much faster than MySQL can and it can do it in parallel, which means it can be 100 times faster in the end, but there is no way to hook it up to standard order by and group by functions so it is implemented in rather hackish way. Same applies to checking number of rows in result set and LIMIT implementation - these can optimized but not by use of MySQL Storage Engine interface, which means it is also has to be implemented through hacks.
In any case this is very interesting how it will be evolving and how more parts in MySQL will become pluggable and so hopefully more innovative tools and techniques will be developed.


Power of MySQL Storage Engines - Read More...