zealfire · March 11, 2018 18:23
diff --git a/MySQL engines b/MySQL engines
 Link: https://stackoverflow.com/questions/4233816/what-are-mysql-database-engines

 MyISAM and InnoDB are the most commonly used engines.

 MyISAM is slightly faster than InnoDB, and implements the FULLTEXT index which is quite useful for integrating search capabilities. MyISAM is not transacted and doesn't implement foreign key constraints, which is a major drawback.

 But you can use the best of both and create tables with different storage engines. Some software (WordPress, I think) use Inno for most data, like relations between pages, versions etc. Records for the posts contain an ID that links to a record in a separate content table that uses MyISAM. That way, the content is stored in the table that has the best search capabilities, while most other data is stored in tables that enforce data integrity.

 If I were you, I'd pick Inno, because it is the most reliable. Only use MyISAM for specific purposes if you need to.

 You can configure your database to use InnoDB by default when creating new tables.


 Storage engines are MySQL components that handle the SQL operations for different table types. InnoDB is the default (for v5.7) and most general-purpose storage engine. MySQL storage engines include both those that handle transaction-safe tables and those that handle nontransaction-safe tables.

 MySQL Supported Storage Engines

 InnoDB: InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints. For more information about InnoDB, see Chapter 14, The InnoDB Storage Engine.

 MyISAM: These tables have a small footprint. Table-level locking limits the performance in read/write workloads, so it is often used in read-only or read-mostly workloads in Web and data warehousing configurations.

 Memory: Stores all data in RAM, for fast access in environments that require quick lookups of non-critical data. This engine was formerly known as the HEAP engine. Its use cases are decreasing; InnoDB with its buffer pool memory area provides a general-purpose and durable way to keep most or all data in memory, and NDBCLUSTER provides fast key-value lookups for huge distributed data sets.

 CSV: Its tables are really text files with comma-separated values. CSV tables let you import or dump data in CSV format, to exchange data with scripts and applications that read and write that same format. Because CSV tables are not indexed, you typically keep the data in InnoDB tables during normal operation, and only use CSV tables during the import or export stage.

 Archive: These compact, unindexed tables are intended for storing and retrieving large amounts of seldom-referenced historical, archived, or security audit information.

 Blackhole: The Blackhole storage engine accepts but does not store data, similar to the Unix /dev/null device. Queries always return an empty set. These tables can be used in replication configurations where DML statements are sent to slave servers, but the master server does not keep its own copy of the data.

 NDB (also known as NDBCLUSTER): This clustered database engine is particularly suited for applications that require the highest possible degree of uptime and availability.

 Merge: Enables a MySQL DBA or developer to logically group a series of identical MyISAM tables and reference them as one object. Good for VLDB environments such as data warehousing.

 Federated: Offers the ability to link separate MySQL servers to create one logical database from many physical servers. Very good for distributed or data mart environments.

 Example: This engine serves as an example in the MySQL source code that illustrates how to begin writing new storage engines. It is primarily of interest to developers. The storage engine is a “stub” that does nothing. You can create tables with this engine, but no data can be stored in them or retrieved from them.

 You are not restricted to using the same storage engine for an entire server or schema. You can specify the storage engine for any table. For example, an application might use mostly InnoDB tables, with one CSV table for exporting data to a spreadsheet and a few MEMORY tables for temporary workspaces.
	Link: https://stackoverflow.com/questions/4233816/what-are-mysql-database-engines

	MyISAM and InnoDB are the most commonly used engines.

	MyISAM is slightly faster than InnoDB, and implements the FULLTEXT index which is quite useful for integrating search capabilities. MyISAM is not transacted and doesn't implement foreign key constraints, which is a major drawback.

	But you can use the best of both and create tables with different storage engines. Some software (WordPress, I think) use Inno for most data, like relations between pages, versions etc. Records for the posts contain an ID that links to a record in a separate content table that uses MyISAM. That way, the content is stored in the table that has the best search capabilities, while most other data is stored in tables that enforce data integrity.

	If I were you, I'd pick Inno, because it is the most reliable. Only use MyISAM for specific purposes if you need to.

	You can configure your database to use InnoDB by default when creating new tables.


	Storage engines are MySQL components that handle the SQL operations for different table types. InnoDB is the default (for v5.7) and most general-purpose storage engine. MySQL storage engines include both those that handle transaction-safe tables and those that handle nontransaction-safe tables.

	MySQL Supported Storage Engines

	InnoDB: InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints. For more information about InnoDB, see Chapter 14, The InnoDB Storage Engine.

	MyISAM: These tables have a small footprint. Table-level locking limits the performance in read/write workloads, so it is often used in read-only or read-mostly workloads in Web and data warehousing configurations.

	Memory: Stores all data in RAM, for fast access in environments that require quick lookups of non-critical data. This engine was formerly known as the HEAP engine. Its use cases are decreasing; InnoDB with its buffer pool memory area provides a general-purpose and durable way to keep most or all data in memory, and NDBCLUSTER provides fast key-value lookups for huge distributed data sets.

	CSV: Its tables are really text files with comma-separated values. CSV tables let you import or dump data in CSV format, to exchange data with scripts and applications that read and write that same format. Because CSV tables are not indexed, you typically keep the data in InnoDB tables during normal operation, and only use CSV tables during the import or export stage.

	Archive: These compact, unindexed tables are intended for storing and retrieving large amounts of seldom-referenced historical, archived, or security audit information.

	Blackhole: The Blackhole storage engine accepts but does not store data, similar to the Unix /dev/null device. Queries always return an empty set. These tables can be used in replication configurations where DML statements are sent to slave servers, but the master server does not keep its own copy of the data.

	NDB (also known as NDBCLUSTER): This clustered database engine is particularly suited for applications that require the highest possible degree of uptime and availability.

	Merge: Enables a MySQL DBA or developer to logically group a series of identical MyISAM tables and reference them as one object. Good for VLDB environments such as data warehousing.

	Federated: Offers the ability to link separate MySQL servers to create one logical database from many physical servers. Very good for distributed or data mart environments.

	Example: This engine serves as an example in the MySQL source code that illustrates how to begin writing new storage engines. It is primarily of interest to developers. The storage engine is a “stub” that does nothing. You can create tables with this engine, but no data can be stored in them or retrieved from them.

	You are not restricted to using the same storage engine for an entire server or schema. You can specify the storage engine for any table. For example, an application might use mostly InnoDB tables, with one CSV table for exporting data to a spreadsheet and a few MEMORY tables for temporary workspaces.