Mysql which engine to use

The storage engines available might depend on which edition of MySQL you are using. InnoDB row-level locking without escalation to coarser granularity locks and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. 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. 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. Archive : These compact, unindexed tables are intended for storing and retrieving large amounts of seldom-referenced historical, archived, or security audit information.

Queries always return an empty set. These tables can be used in replication configurations where DML statements are sent to replica servers, but the source server does not keep its own copy of the data. 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. In addition, in the case with multiple storage engines configured, it would be better to disable the unused storage engines to improve server performance.

For example, you have ten configured storage engines but use only one of them. In this case, nine storage engines stand idle and only consume server resources with bringing no benefit. Its peculiarity is that it allows accessing the data from a remote MySQL database. At the same time, it does not need replication or cluster technologies. The key is the local Federated table. When a query addresses that table, it applies automatically to the remote federated table. Remote servers store the data, while the local server only points to them with the connection string.

The efficiency of this action does not depend on the storage engine type on the remote server. However, Federated has a dangerous drawback. It is far from being the best solution when dealing with joined tables, as its speed of work decreases tremendously in that case.

Besides, its treatment of transactions is rather weird. The engine has a narrow specialization and should be addressed with care. Yes means that the feature is supported and No — not supported. MySQL, due to its all-embracing nature, is a must-know for database developers.

Lots of things have to be taken care of before the project start. Choosing the appropriate MySQL storage engine always has the highest priority. Fortunately, the variety of available options is comprehensive enough to let you choose the most suitable solution for any case.

Download a free day trial version of dbForge Studio for MySQL to evaluate the features and capabilities the tool provides. The MEMORY engine is the best way to perform a large number of complex queries on data that would be inefficient to search on a disk based engine. The CSV engine is a great way to export data that could be used in other applications.

BDB is excellent for data that has a unique key that is frequently accessed. A database engine or storage engine is the underlying software component that a database management system DBMS uses to create, read, update and delete CRUD data from a database.

Most database management systems include their own application programming interface API that allows the user to interact with their underlying engine without going through the user interface of the DBMS. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more.

What are MySQL database engines? Asked 10 years, 11 months ago. Active 3 years, 1 month ago. Viewed 66k times. Improve this question. Somnath Muluk Related: serverfault.