PHPBuilder - New Storage Engines in MySQL 5



RSS Twitter
Articles Databases

New Storage Engines in MySQL 5

by: Ian Gilfillan
|
March 29, 2007

MySQL 5 offers a number of new storage engines (previously called table types). In addition to the default MyISAM storage engine, and the InnoDB, BDB, HEAP and MERGE storage engines, there are four new types: CSV, ARCHIVE, FEDERATED and EXAMPLE, as well as a new name for the HEAP storage engine. It is now called the MEMORY storage engine. None of the new types are available by default - you can check for sure with the SHOW ENGINES statement. Here is what is on my default version of MySQL Max:


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        | YES     | Supports transactions and page-level locking               |
| BERKELEYDB | YES     | 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                                         |
+------------+---------+------------------------------------------------------------+


To add support for the missing storage engines, you currently need to build MySQL with certain options. It is likely though that there will be binary versions that include these storage engines by default at some point. Until then, there is no other way to enable them.


Changes in the MEMORY storage engine


You can read my article on the HEAP table type as a start, as most of the detail has not changed, and refer below for modifications in MySQL 5.
Previously, the HEAP storage engine only made use of hash indexes. These allow finding specific matches extremely quickly, but do not return any kind of range data. An index matches a record, but there is no ordering to allow it to return subsequent records. Only the complete index can be used, the concept of leftmost prefixing (using the left part of an index) does not apply. The MEMORY storage engine now permits BTREE indexes as well (the kind used by MyISAM tables by default).
To specify an index type, use the USING clause, as in the following examples:


CREATE TABLE memory_table (f1 INT, INDEX USING BTREE (f1)) ENGINE = MEMORY;


or:


CREATE TABLE memory_table (f1 INT, INDEX USING HASH (f1)) ENGINE = MEMORY;


The HASH index is still the default, and will be the type of index created if you do not specify a particular kind.
  • Now support AUTO_INCREMENT
  • Now support INSERT DELAYED
  • Support indexes on columns that can contain NULL values
  • Never get converted to disk tables. (Temporary internal tables are automatically converted to disk table if they get too big, MEMORY tables never are. The max_heap_table_size variable (it hasn't yet changed its name to reflect the new storage engine name) places a limit on the memory utilization of MEMORY tables, and you can always place a MAX_ROWS limit as well, when creating the table.
The EXAMPLE storage engine


Added in MySQL 4.1.3 and only of interest to developers, the EXAMPLE storage engine does nothing, but is there to provide simple source code for developers to base new storage engines on. For those interested, the source code can be found in the sql/examples directory.


The FEDERATED storage engine
Added in MySQL 5.0.3, to make use of it you need to use the --with-federated-storage-engine option to configure when building MySQL. The FEDERATED storage engine allows you to access data from a table on another database server. That table can make use of any storage engine. Let's see it in action. First, CREATE a table on a remote server (you can do this on the same server for testing purposes, but doing so is fairly pointless otherwise).


CREATE TABLE myisam_table (f1 INT, PRIMARY KEY(f1)) 
ENGINE=MYISAM;


Assuming that the default is set to create MyISAM tables (FEDERATED tables can access tables of any type), the above statement creates a definition file (.frm), an index file (.MYI) and a data file (.MYD). If you had created an InnoDB file, MySQL would create a definition (.frm) and index and data file (.idb). Now create the FEDERATED table on another server. The original table must always exist first:


CREATE TABLE federated_table (f1 INT, PRIMARY KEY(f1)) 
ENGINE=FEDERATED 
COMMENT='mysql://username:password@hostname.co.za:3306/dbname/myisam_table';



1
|
2
Next Page »

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:
Ian Gilfillan

Comment:



Comment:

(Maximum characters: 1200). You have characters left.