After digging up a bit I found that:
[ul][li]charges longest string is “-1 -1 -1 -1 -1” (15 characters). Therefore it’s useless to keep it as a text.[/li]
[li]my enchantments longest string is "3854 0 0 3620 1544473 0 3559 0 0 3559 0 0 3559 0 0 3753 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 " (96 chars). Useless to keep it text.[/li]
[li]my text longest string is [/li][SPOILER]“Dear $N,$B$BTales of your recent performance in the Trial of the Grand Crusader will be told,and retold,for ages to come. As the Argent Crusade issued its call for the greatest champions of Azeroth to test their mettle in the crucible of the Coliseum,I hoped against hope that beacons of light such as you and your companions might emerge from the fray.$B$BWe will need you direly in the coming battle against the Lich King. But on this day,rejoice and celebrate your glorious accomplishment and accept this gift of one of our very finest warhorses. When the Scourge see its banner looming on the horizon,hero,their end shall be nigh!$B$BYours with Honor,$BTirion Fordring”
[/SPOILER](672 chars).
[/ul]
Considering that:
Varchar is for 255 chars max (it’s useless to use it for higher values) and requires L+1 bytes for each row
Text is for 65536 chars max and requires L+2 bytes for each row
Longtext is for 4294967296 characters max and requires L+4 bytes for each row
(where L is the string length)
I suggest this edit:
ALTER TABLE `item_instance`
CHANGE `charges` `charges` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
CHANGE `enchantments` `enchantments` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
CHANGE `text` `text` text CHARACTER SET utf8 COLLATE utf8_general_ci;[/sql]
It saved up to 400mb on that single table (26% of its size)
References:
For data type size: http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html
To check what’s the longest string I used this query:
[sql]SELECT data_column FROM item_instance WHERE LENGTH(TRIM(data_column)) = (SELECT MAX(LENGTH(TRIM(data_column))) FROM item_instance)
where data_column was: charges, enchantments and text