Mysql – How is it possible for thesql storage engine to be NULL

Looking at the feature availability list at http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html two possible problems jump out:

1. No transaction or FK support, meaning you will have to manage transactional integrity and referential integrity in your own code were needed (which could end up being a lot less efficient than letting the DB do this for you, though that very much depends on your app's expected behaviour patterns).
2. Table level locking only: this could be a significant barrier to scalability if your app needs multiple concurrent writers to the same set of tables or in cases where your read operations use locks to ensure consistent data is read - in such cases a disk based table that supports much finer lock granularity will perform far better if enough of its content is currently cached in RAM.

Other than that, assuming you have enough RAM, a memory based table should be faster than a disk based one. Obviously you need to factor in taking snapshots to disk to address the issue of what happens when the server instance is reset, which is likely to completely negate the performance benefit overall if the data needs capturing often (if you can live with losing a day of data in such an instance you could just take a backup once per day, but in most cases that would not be acceptable).

An alternative might be to:

1. Use disk based tables, but ensure that you have more than enough RAM to hold them all in RAM at any given time (and "enough RAM" might be more than you think as you need to account for any other processes on the machine, OS IO buffers/cache and so forth)
2. Scan the entire contents (all data and index pages) of the table on each startup to preload the content into memory with SELECT * FROM <table> ORDER BY <pkey fields> for each table followed by SELECT <indexed fields> FROM <table> ORDER BY <index fields> for each index

This way all your data is in RAM, you only have to worry about I/O performance for write operations. If your app's common working set is much smaller than the whole DB (which it usually the case - in most applications most users will only be looking at the most recent data most if the time) you might be better of being more selective about how much you scan to preload into memory, allowing the rest to be loaded from disk on demand.

IMHO it is really about security. If a table uses the federated storage engine, all one needs to do is this :

SHOW CREATE TABLE fedtblname\G

and you can see the connection information (server IP, database, table name, username, password) of the real MyISAM table on the remote DB server, and all in plain text.

It is too bad that the connection information is not encrypted.

The bottom line is that you can use federated option in my.cnf totally at your own risk.

UPDATE 2012-01-18 11:31 EDT

It was pointed out that you can create a server definition and then define that server definition in the CONNECTION option of the federated table.

I tested this out and it works great.

However, there is still a security flaw. If you can SELECT from the table mysql.servers, you can see all definitions. Here is the table's layout:

mysql> show create table mysql.servers\G
*************************** 1. row ***************************
       Table: servers
Create Table: CREATE TABLE `servers` (
  `Server_name` char(64) NOT NULL DEFAULT '',
  `Host` char(64) NOT NULL DEFAULT '',
  `Db` char(64) NOT NULL DEFAULT '',
  `Username` char(64) NOT NULL DEFAULT '',
  `Password` char(64) NOT NULL DEFAULT '',
  `Port` int(4) NOT NULL DEFAULT '0',
  `Socket` char(64) NOT NULL DEFAULT '',
  `Wrapper` char(64) NOT NULL DEFAULT '',
  `Owner` char(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`Server_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL Foreign Servers table'
1 row in set (0.00 sec)

mysql>

In order to see the server definitions, run this query:

SELECT * FROM mysql.servers\G

This will expose all remote servers known by the MySQL Instance, including the password in plain text.

To see all the federated tables, run this:

select table_schema,table_name from information_schema.tables where engine='federated';

This confirms my original assertion: all one needs to do is this :

SHOW CREATE TABLE fedtblname\G

and you can see the connection information (server IP, database, table name, username, password) of the real MyISAM table on the remote DB server, and all in plain text. As given by my update, that plain text info is located in mysql.servers.