PHPBuilder - New Storage Engines in MySQL 5 Page 2

RSS Twitter

New Storage Engines in MySQL 5 - Page 2

by: Ian Gilfillan
March 29, 2007

This creates a definition file (.frm), but the data and index files are that of the table on the remote server. The only unusual syntax is the COMMENT, which supplies the username, password (optional), port (optional), database and table name. This method is not particularly elegant, or secure, as the password is stored in clear text available to anyone who has access to the table data. However, in most cases it is likely that whoever has access to the FEDERATED table can also have access to the remote table, so this should not be too much of an issue. Bear in mind that this method of connecting will likely change in a future version.

There are some limitations on the use of FEDERATED tables. They are useful for easily accessing data on a different server, but fall short in many areas:

  • No Data Definition Language statements are permitted (such as DROP TABLE, ALTER TABLE)
  • They do not support transactions (since the remote server is contacted once, and the results returned to the local server)
  • Similarly, there is no way of being sure that the integrity of the local data is intact.
  • No prepared statements.
The CSV storage engine

Added in MySQL 4.1.4, tables of type CSV are actually just comma-delimited text files. This can be quite useful, and they exist to allow MySQL to interact easily with other applications that make use of CSV files, such as spreadsheets. They make no use of any sort of indexing. To enable this storage engine, use the --with-csv-storage-engine configure option when building MySQL.

Let's see how this works. You can import an existing CSV file. Assume you have a file containing firstname, surname and age, as follows:


First, create the .frm definition file, as follows:

mysql> CREATE TABLE csv_names(firstname CHAR(30), surname CHAR(40), age INT) ENGINE = CSV;

The blank data file is also created. Since the CSV file is just a plain text file, you can copy an existing CSV file into the same location, and it will be viewable from the MySQL client, as follows:

mysql> SELECT * FROM csv_names;
| firstname | surname    | age |
| Jacob     | Mbezela    |  42 |
| Schabir   | Field      |  29 |

With no indexes, the SELECT is not at all efficient, and performs a complete table scan. Conversely, you can INSERT a record from the MySQL client:

mysql> INSERT INTO csv_names VALUES('Quinton','Baxter','75');

and view the change in the CSV file:


The ARCHIVE storage engine

Added in MySQL 4.1.3, the archive storage engine lives up to its name by storing large amounts of data without taking up too much space. It too makes no use of any sort of indexing, and there are no means to repair the table should it become corrupted during a crash. To enable this storage engine, use the -with-archive-storage-engine configure option when building MySQL.

mysql> CREATE TABLE archive_names(firstname CHAR(30), surname CHAR(40), age INT) ENGINE = ARCHIVE;

This, as always, creates a .frm definition file, as well as .ARZ and .ARM data and metadata files.

Being an archive, you cannot DELETE, UPDATE or REPLACE records - you can only INSERT and SELECT. Again, with no indexes, the SELECT needs to perform a complete table scan. Although the records are compressed upon insertion, OPTIMIZE TABLE can compress the entire dataset even further. A .ARN file will temporarily appear when this occurs.

mysql> INSERT INTO archive_name VALUES('Quinton','Baxter','75');

mysql> SELECT * FROM archive_names;
| firstname | surname    | age |
| Quinton   | Baxter     |  75 |


The new storage engines, whilst tricky for most people to use since they require rebuilding MySQL, can be useful for those with special needs. After all, people have taken the time to write them! They are bound to be included in the binaries at some point, so if you are dying to try them, but rebuilding MySQL is not for you, you hopefully do not have to be patient for long. Even if you are able to use them now, keep an eye on the official documentation, as things are likely to change as they approach maturity. Good luck!

This article originally appeared on

« Previous Page

Comment and Contribute

Your comment has been submitted and is pending approval.

Ian Gilfillan



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