The MySQL Adapter uses the MM.MySQL JDBC adapter. The adapter has been optimized for the use of this JDBC driver and side steps some performance issues in the driver for maximum efficiency (using Statements instead of PreparedStatements). The Mysql adapter is very fast, and is certainly more optimized then all the other adapters at this point. Until the WorkManager is complete (which would allow very easy multithreaded database requests), if you wish to tweak a bit of extra speed out of Storable use a TransactionTracker instead of a ManagerTracker when making Storable requests. This saves the overhead of fetching a new database connection out of the pool. When you fetch a connection from the pool, it will actually check to make sure the database connection is alive (using a SELECT 1), if you make dozens and dozens of Storable requests, the extra queries (of SELECT 1) can add up. By using a TransactionTracker (even though there's no "transaction" in mysql) you will use the same database connection and save a bit of horsepower.
MySQL's MyISAM tables do not support transactions, as a result, no transactions can be performed on these tables. MySQL is currently developing transactions using BDB tables. MySQL can capably use transactions on these tables, however I must add a word of caution. In my opinion, MySQL's implementation of transactional isolation is entirely unreasonable. Before you think about using transactions on mysql, try them out. You'll discover if another (non-committed) transaction inserted a record into a table, all other transactions are locked out of inserting/selecting from that table. If another transaction (non-committed) has selected from a table, no other transaction can insert into it. When you run into these cases, one transaction or the other halts until the other transaction commits or rollsback. This almost makes it impossible that more then one transaction could possibly be happening at once if they use any of the same tables. Although it ultimately implements serializable transaction isolation, it's hard to believe a useful application being built when the database can only allow one transaction at a time or things start deadlocking or stalling. At least that's what I've learned from using MySQL's transactions for about an hour, things may change.
I have used MySQL in several production projects, and there should never be any known issues with it (only enhancements!). It's by far the most veteran database adapter.
Although the MySQL adapter is fairly well optimized, I know of a few optimizations which can reduce some overhead involved with doing loadInstance and loadVectorInstance. Specifically, removing the check for the existence of the table before executing the query. If a table was missing, parse the error after the query, rather then proactively checking. This should increase performance for most queries, but slow down queries where a table in the WhereQuery is not present. I believe insert() still uses PreparedQuery, which in the MM.MySQL JDBC driver is extremely slow.
There is a small problem when storable is used against a MySQL server running on the win32 platform. MySQL by default mangles the case of the table names storable generates. The result is storable will create a table with mixed case, but MySQL will silently translate it to lowercase. Later when storable queries looking for the table, the table no longer respond to the mixed case table name (even though that's how it was created). To work around this problem you must turn off table name mangling in the MySQLD. Edit your c:\my.cnf file, and add this line:
set-variable = lower_case_table_names=0
in the [mysqld] section, and restart your win32 MySQL server. If you do not have a c:\my.cnf file, copy my-example.cnf out of your MySQL server directory place it at c:\my.cnf, and add the variable above. Once that variable is added, you should have trouble free Storable service.