September 17, 2014
 
 
RSSRSS feed

MySQL Storage Engines - page 2

What is a Storage Engine?

  • October 7, 2005
  • By Martin C. Brown

You can determine a list of engines by using the show engines command within MySQL (assuming a MySQL server version later than 4.1.2).

mysql> show engines;
+------------+---------+------------------------------------------------------------+
| Engine     | Support | Comment                                                    |
+------------+---------+------------------------------------------------------------+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     |
| HEAP       | YES     | Alias for MEMORY                                           |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  |
| MERGE      | YES     | Collection of identical MyISAM tables                      |
| MRG_MYISAM | YES     | Alias for MERGE                                            |
| ISAM       | NO      | Obsolete storage engine, now replaced by MyISAM            |
| MRG_ISAM   | NO      | Obsolete storage engine, now replaced by MERGE             |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys |
| INNOBASE   | YES     | Alias for INNODB                                           |
| BDB        | NO      | Supports transactions and page-level locking               |
| BERKELEYDB | NO      | Alias for BDB                                              |
| NDBCLUSTER | NO      | Clustered, fault-tolerant, memory-based tables             |
| NDB        | NO      | Alias for NDBCLUSTER                                       |
| EXAMPLE    | NO      | Example storage engine                                     |
| ARCHIVE    | NO      | Archive storage engine                                     |
| CSV        | NO      | CSV storage engine                                         |
+------------+---------+------------------------------------------------------------+
16 rows in set (0.01 sec)

The listing shows the full list of available database engines, and whether the support is available in the current database server.

For versions of MySQL earlier than 4.1.2, use

mysql> show variables like "have_%";
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| have_bdb         | YES      |
| have_crypt       | YES      |
| have_innodb      | DISABLED |
| have_isam        | YES      |
| have_raid        | YES      |
| have_symlink     | YES      |
| have_openssl     | YES      |
| have_query_cache | YES      |
+------------------+----------+
8 rows in set (0.01 sec)          

You can configure the available engines within a MySQL installation by changing the options to the configure script. If you are using a pre-packaged MySQL binary distribution then most of the commonly used engines are included. Note, however, that if you want access to some of the more unusual types (particulalry the CSV, ARCHIVE and BLACKHOLE engines) then you may want to build your MySQL dsatabase by hand.

Sitemap | Contact Us