Understanding MySQL Internals

Chapter 10. Storage Engines

In this chapter we will discuss the most prominent storage engines within MySQL in more detail. Unfortunately, due to the large number of different storage engines and the complexity that some possess, we are not able to examine each one in sufficient detail on the code level. Indeed, storage engines like MyISAM and InnoDB each deserve their own thousand-page book. However, I will provide pointers to the source for those who would like to learn more.

Different storage engines have different capabilities. Table 10-1 contains a comparison of different storage engines.

Table 10-1. MySQL storage engine comparison

MyISAM	InnoDB	Memory	Merge	NDB	Archive	Federated
Transactions	No	Yes	No	No	Yes	No	No
Indexing	B-tree, R-tree, full text	B-tree	Hash, B-tree	B-tree, R-tree	Hash, B-tree	None	Depends on the remote table engine
Storage	Local disk	Local disk	RAM	Local disk	Remote and local duster nodes	Local disk	Remote MySQL server instance
Caching	Key cache	Key and data cache	N/A	Same as MyISAM	Key and data cache	None	Depends on the remote table engine
Lacking	Table	Row	Table	Table	Row	Row	Relies on the remote table engine
Foreign keys	No	Yes	No	No	No	No	Depends on the remote table engine

Shared Aspects of Architecture

While there is a great degree of freedom in the implementation of a storage engine, all storage engines must integrate with the main MySQL server code. As a result they have a few things in common. Aside from having to support the basic concepts of tables residing in a database, records, columns, ...