Chapter 16 Alternative Storage Engines Table of Contents 16.1 Setting the Storage Engine .................................................................................................... 3400 16.2 The MyISAM Storage Engine ................................................................................................ 3401 16.2.1 MyISAM Startup Options ............................................................................................ 3404 16.2.2 Space Needed for Keys ............................................................................................. 3406 16.2.3 MyISAM Table Storage Formats ................................................................................. 3406 16.2.4 MyISAM Table Problems ............................................................................................ 3409 16.3 The MEMORY Storage Engine .............................................................................................. 3410 16.4 The CSV Storage Engine ...................................................................................................... 3415 16.4.1 Repairing and Checking CSV Tables .......................................................................... 3415 16.4.2 CSV Limitations ......................................................................................................... 3416 16.5 The ARCHIVE Storage Engine .............................................................................................. 3416 16.6 The BLACKHOLE Storage Engine ......................................................................................... 3418 16.7 The MERGE Storage Engine ................................................................................................. 3420 16.7.1 MERGE Table Advantages and Disadvantages ........................................................... 3423 16.7.2 MERGE Table Problems ............................................................................................ 3424 16.8 The FEDERATED Storage Engine ......................................................................................... 3426 16.8.1 FEDERATED Storage Engine Overview ...................................................................... 3426 16.8.2 How to Create FEDERATED Tables ........................................................................... 3427 16.8.3 FEDERATED Storage Engine Notes and Tips ............................................................. 3430 16.8.4 FEDERATED Storage Engine Resources .................................................................... 3431 16.9 The EXAMPLE Storage Engine ............................................................................................. 3431 16.10 Other Storage Engines ........................................................................................................ 3432 16.11 Overview of MySQL Storage Engine Architecture ................................................................. 3432 16.11.1 Pluggable Storage Engine Architecture ...................................................................... 3433 16.11.2 The Common Database Server Layer ....................................................................... 3433 Storage engines are MySQL components that handle the SQL operations for different table types.  InnoDB is the default and most general-purpose storage engine, and Oracle recommends using it for tables except for specialized use cases. (The  CREATE TABLE  statement in MySQL 8.0 creates  InnoDB  tables by default.) MySQL Server uses a pluggable storage engine architecture that enables storage engines to be loaded into and unloaded from a running MySQL server. To determine which storage engines your server supports, use the  SHOW ENGINES  statement. The value in the  Support  column indicates whether an engine can be used. A value of  YES ,  NO , or  DEFAULT indicates that an engine is available, not available, or available and currently set as the default storage engine. mysql>  SHOW ENGINES\G *************************** 1. row *************************** Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance Schema Transactions: NO XA: NO Savepoints: NO *************************** 2. row *************************** Engine: InnoDB Support: DEFAULT 3397