Re: MySQL 5.0 + Innodb plugin

[Aurelien PANIZZA]

I tried InnoDb compression (MySQL 5.5) with KEY_BLOCK_SIZE=16, which did a great job for TEXT field (around 90% compression). MySQL 5.5 has a bug with query like 'insert into ... on duplicate key ..'. We use a lot this kind of query in our scripts, so I can't upgrade to MySQL 5.5.
I may upgrade to MariaDB 5.5 since this bug doesn't seem to be present.
I'm looking for a way to compress TEXT fields (which represent 80% of my table size), but as usual, the project manager doesn't want to change the source code (I was thinking of COMPRESS() and UNCOMPRESS() functions, which seem to be pretty efficient).
With MySQL 5.0 I can't think of many possibilities

- MyISAM compress option is read only and didn't compress well (in my test case)
- Archive engine doesn't support indexes (I've thought to export only the text field in a new ARCHIVE table and create a view which aggregate the two tables, so that no need to change the source code)
- COMPRESS() and UNCOMPRESS() functions, but we must change the source code
- The TEXT field stores XML files, so store the files only on the disk and keep a link towards the XML file in the table, but we must change the source code
- Upgrade to MariaDB 5.5 and use InnoDB compression.