Optimize DB schema for InnoDB on frequently used tables

MySQL+InnoDB is probably the most widely used DB with Drupal installations. So it makes sense to optimize DB schemas for InnoDB. Probably other RDBMS solutions will benefit from optimizations too. We sould focus on optimizing schemas on tables that are used most frequently and commonly. Those are tables that are used during Drupal boostrap. Also all kinds of cache storage tables should be optimized if possible.

Columnt types like TEXT and BLOB have negative impact on InnoDB performance as those columns are always stored off-page at InnoDB pyshical storage format.

If all TEXT and BLOB columns can be replaced with VARCHAR columns on a table schema then SELECT...WHERE... performance on that table is almost doubled in certain cases. It doesn't speed up full table scans, but it will speed up queries using primary indexes. If not all TEXT and BLOB columns can be replaced within a table then it doesn't make much sense to replace any of them in a table schema. To get performance boost as described average row lenght must be half of one InnoDB page size, which is by default 16k.

One example candidate of such table that can be optimized is "variable" table from system.module. Column "value" is currently defined as BLOB and probably could be replaced with VARCHAR(16384). It's not the best example as variable table doesn't usually grow very much. Bigger the table, bigger the performance boost will be. Altough table "variable" is good candidate for optimization as average row lenght is smaller than half of one InnoDB page.

Maybe we could list candidate table schemas within comments on this issue and decide if such schema optimizations should be done.