MySQL Storage Engines - page 5
What is a Storage Engine?
MyISAM
The MyISAM engine is the default engine in most MySQL installations and is a derivative of the original ISAM engine type supported in the early versions of the MySQL system. The engine provides the best combination of performance and functionality, although it lacks transaction capabilities (use the InnoDB or BDB engines) and uses table-level locking.
With table-level locking, a database with a high number of row inserts or updates becomes a performance bottleneck as the table is locked while data is added. Luckily this limitation also works well within the restrictions of a non-transaction database.
Table 1. MyISAM Summary
Name | MyISAM |
Introduced | v3.23 |
Default install | Yes |
Data limitations | None |
Index limitations | 64 indexes per table (32 pre 4.1.2); Max 16 columns per index |
Transaction support | No |
Locking level | Table |
MERGE
The MERGE engine type allows you to combine a number of identical tables into a single table. You can then execute queries that return the results from multiple tables as if they were just one table. Each table merged must have the same table definition.
The MERGE table is particularly effective if you are logging data directly or indirectly into a MySQL database and create an individual table per day, week or month and want to be able to produce aggregate queries from multiple tables. There are limitations to this however, you can only merge MyISAM tables and the identical table definition restriction is strictly enforced. Although this seems like a major issue, if you had used one of the other table types (for example InnoDB) then the merge probably wouldn't be required.
Table 2. MERGE Summary
Name | MERGE | ||||||||||
Introduced | v3.23.25 | ||||||||||
Default install | Yes | ||||||||||
Data limitations | Underlying tables must be MyISAM | Index limitations | N/A | Transaction support | No | Locking level | Table | |
MEMORY
The MEMORY storage engine (previously known as the HEAP storage engine) stores all data in memory; once the MySQL server has been shut down any information stored in a MEMORY database will have been lost. However, the format of the individual tables is kept and this enables you to create temporary tables that can be used to store information for quick access without having to recreate the tables each time the database server is started.
Long term use of the MEMORY storage engine is not generally a good idea, because the data could so easily be lost. However, providing you have the RAM to support the databases you are working on, use of MEMORY based tables is an efficient way of running complex queries on large data sets and benefitting from the performance gains.
The best way to use MEMORY tables is to use a SELECT statement to select a larger data set from your original, disk-based, tables and then sub-analyse that information for the specific elements you want. I've used this technique in the past to extract a month worth of web log data, actually from tables using the ARCHIVE storage engine, and then run the queries on specific URLs, sites and other focus points.
Table 3. MEMORY Summary
Name | MEMORY (HEAP, deprecated) |
Introduced | 1.0 (only known as MEMORY since 4.1) |
Default install | Yes |
Data limitations | BLOB and TEXT types not supported |
Index limitations | None |
Transaction support | No |
Locking level | Table |
EXAMPLE
The EXAMPLE engine is actually a programming example of a storage engine that can be used as the basis for other engines within the MySQL system. It does not support data inserts and isn't a practical engine for any form of database access. It is, however, a good guide to how to develop your own storage engine, and is therefore an effective guide for programmers.
Table 4. EXAMPLE Summary
Name | EXAMPLE |
Introduced | v4.1.3 |
Default install | No |
Data limitations | N/A |
Index limitations | N/A |
Transaction support | N/A |
Locking level | N/A |