If you want to do fast analysis on very large datasets or you need to extract a small sliver of data from a very wide table or set of tables, check out the free version of the Info-Bright MySql data storage engine. The non-commercial version is called ICE (InfoBright Community Edition.) http://www.infobright.org It's intended for data warehouse and archival applications.
A crucial point about ICE is that it's read-only. It doesn't support insert, delete or update. You can create a schema (with some restrictions,) and you can do any other SQL supported by MySql. What this means is that to get data into ICE tables you have to use 'load data infile'. The commercial version doesn't have that restriction.
So putting your Rails app on ICE is probably out. But what if you want to use Rails migrations to generate a schema with ICE or otherwise use Ruby Active-Record to access an ICE database?
I downloaded the Linux 64 bit version of ICE from the InfoBright website and set it up; it was basically a stand-alone version of MySql with the InfoBright storage engine built in. I was able to connect to it using the standard MySql database adapter, but attempting to re-create a schema from my Migration classes resulted in errors from MySql. It turns out ICE, because it doesn't support 'insert', doesn't allow primary keys or auto_increment type columns. In a read-only situation this is okay, but it means your old DDL won't work.
I needed to replace the DDL generated in the 'create_
table' method in migrations with something that ICE would accept. In every ActiveRecord database adapter class there's a definition of the data types for the database system in question. MySql looks like
[Read More]