Back to articles list
- 7 minutes read

Using Different MySQL Storage Engines in Database Design

Any database architect designing a MySQL database faces the issue of selecting the proper storage engine. Usually, an application uses only one engine: MyISAM or InnoDB. But let’s try to be a little more flexible and imagine how different storage engines can be used.

The Initial Data Model

To begin, let’s build a simplified data model for a CRM (customer relationship management) system that we’ll use to illustrate the point. The design will cover the main CRM functions: sales data, product definitions, and information for analytics. It won’t contain details typically used in CRM systems.

As you can see, this data model has tables that store transactional information called sale and sale_item. When a customer purchases something, the application will create a new row in the sale table. Each purchased product will be will be reflected in the sale_item table. A related table, sale_status, is for storing possible statuses (i.e., pending, complete, etc.).

The product table stores information about goods. It defines each product and its basic descriptors. In a more detailed diagram, I would add more tables to handle product specification and categorization. But for our current needs, that’s not necessary.

The customer table keeps data about clients. This is an integral part of any CRM system and it usually tracks all users’ individual activity. Obviously, it often has really detailed information. But as I noted, we don’t need these details right now.

The log table stores what each customer did within the application. And the report_sales table is designed for data analytics usage.

Next, I’ll describe the MySQL storage engines that could possibly be used in this design. And later, we’ll discuss which engine is suitable for each type of table.

An Overview of MySQL Storage Engines

A storage engine is a software module MySQL uses to create, read, or update data from a database. It’s not recommended to randomly choose an engine, but many developers are happy to use either MyISAM or InnoDB, although other options are also available. Each engine has its own pros and cons, and proper engine selection depends on several factors. Let’s take a look at the most popular engines.

  • MyISAM has a long history with MySQL. It was the default engine for MySQL databases before the 5.5 release. MyISAM does not support transactions and has only table-level locking. It is mostly used for read-intensive applications.
  • InnoDB is a general storage engine that balances high reliability and good performance. It supports transactions, row-level locking, crash recovery, and multi-version concurrency control. Also, it provides a foreign key referential integrity constraint.
  • The Memory engine stores all data in RAM. It can be used for storing lookup references.
  • Another engine, CSV, keeps data in text files with comma-separated values. This format is mostly used for integration with other systems.
  • Merge is a good choice for reporting systems, such as in data warehousing. It allows the logical grouping of a set of identical MyISAM tables, which can also be referenced as one object.
  • Archive is optimized for high-speed inserting. It stores information in compact, unindexed tables and does not support transactions. The Archive storage engine is ideal for keeping large amounts of seldom-referenced historical or archived data.
  • The Federated engine offers the ability to separate MySQL servers or to create one logical database from many physical servers. No data is stored on the local tables and queries are automatically executed on the remote (federated) tables.
  • The Blackhole engine acts as a “black hole” that accepts data but doesn’t store it. All selects return an empty dataset.
  • The engine Example is used to show how to develop new storage engines.

This is not a full list of storage engines. MySQL 5.x supports nine of them straight from the box, plus dozens more developed by the MySQL community. More details about storage engines can be found on MySQL’s official documentation.

Updating the Data Model Design

Look again at our data model. Obviously, different tables will be used in different ways. The sale table must support transactions. On the other hand, the log and report_sales tables do not require this feature. The main mission of the log table is storing data with maximum efficiency. Quick retrieval is the main requirement for the report_sales table.

Let’s keep in mind points above and modify our database schema. In Vertabelo, you can set “Storage engine” in the Table Properties panel. Please have a look at the pictures below.

Setting the storage engine

So, let’s see updated database design.

I specified storage engines for existing tables and reorganized the report_sales table. As you can see, the tables are divided into three groups:

  • Transaction tables, which are used with the main application
  • Report tables for BI analytics
  • Log table for storing all user activity

Let’s talk about all of them separately.

Transaction Tables

These tables contain data entered by users during daily routine operations. In our case, there would be sale information, such as:

  • which employee made the sale
  • who purchased the product
  • what was sold
  • how much it cost

In a majority of cases, InnoDB is the best solution for transaction tables. This storage engine supports row locking, and some users are able to work together. Likewise InnoDB allows the use of transactions and foreign keys. But, as you know, these benefits are not free; the engine may perform select statements more slowly than MyISAM and save data with less efficacy than Archive.

All of the engines described above do have some protections in place, so developers don’t have to write complex rollback functions for each operation. In a typical sales application keeping the consistency of data is more important than possible performance issues.

Report Tables

In the new design, I divided one table into a couple of smaller tables. This saves effort when it comes time to manage data and perform table and index maintenance. It also allows us to create the MERGE table sale_report for combining other report tables. As result, the BI tool still retrieves data from one huge table (for analytics purposes), but we have the benefit of working with smaller tables.

The Report_sale_{year} tables are MyISAM tables. This storage engine doesn’t support transactions and can only lock the table as a whole. Because MyISAM doesn’t worry about these complex items, it performs data manipulation operations at speed. Because of its file structure, this storage engine reads data faster than the more popular InnoDB.

The Log Table

The Archive storage engine is good choice for storing log data. It can insert rows and compress stored data quickly. There are great benefits for keeping information about user activities. However, Archive has some restrictions. It does not support update operations, and it retrieves data slowly. But in a log table, the described benefits are more important than the drawbacks.

Integrating Storage Engines

Each system must be integrated with external life. For applications, this can be users who populate reference and transaction tables. It can be services and integration via REST, SOAP, WCF or something like this. And last but not least, it can be database integration.

MySQL and Oracle have developed two really helpful storage engines: Federated and CSV. The first one, Federated, should be used for loading data from an external MySQL database. The second storage engine, CSV, allows databases to save records in CSV format and read comma-separated files on air, without any additional efforts.

As you can see, using different storage engines for different purposes gives your database greater flexibility. If a database architect makes their decision after considering all the pros and cons, then the result can be really impressive.

Do you have experience using different storage engines in database design? I would like to see your tips and suggestions. Please share them in the comments section.

go to top