MySQL Storage Engines

MySQL as a relational database offers a unique feature in the management of data by offering different storage engines that define different characteristics for the persistence and retrieval of your information.

Each storage engine offers relative strengths and weaknesses, the choice of one engine for one task may not be ideal for another. MySQL supports the use of multiple storage engines in a single schema, however complexity in execution, functionality such as transaction support and backup strategy are all affected.

Beginning with MySQL 5.1, MySQL offers the pluggable storage engine architecture (PSEA), where it is possible for a vendor to provide a runtime storage engine that can be loaded dynamically in an operational environment. While the theory enables this, in practice only a few engines have been able to achieve this. MySQL limitation on parsing and optimizing SQL only the way MySQL thinks, the lack of pushdown conditions to the engine, and the changes in the specification in point releases leading to incompatibilities, has made it impossible for creative solutions with optimized data management to work seamlessly. Many providers have been forced to produce custom MySQL binaries.

Some of the different features of storage engines include:
• Transactional and non transactional
• Persistent and non persistent
• Table and row level locking
• Different index methods such as btree, hash and rtree
• Clustered indexes, primary and secondary
• Data compression
• Full text searching