database optimization

SELECT COUNT(*) FROM characters; 47262
SELECT COUNT(*) FROM characters WHERE trans_x <>0; 39
need sql to move characters.trans_x, characters.trans_y, characters.trans_z, characters.trans_o, characters.transguid to characters_trans

SELECT COUNT(*) FROM characters WHERE at_login <>0; 2624
need sql to move characters.at_login to characters_at_login

SELECT COUNT(*) FROM characters WHERE drunk <>0; 31
need sql to move characters.drunk to characters_drunk

SELECT COUNT(*) FROM characters WHERE grantableLevels <>0; 194
need sql to move characters.grantableLevels to characters_grantableLevels

SELECT COUNT(*) FROM `characters` WHERE `stable_slots` <> '0' LIMIT 0, 1000; 2995

SELECT * FROM `characters` WHERE `taxi_path` <> '' LIMIT 0, 1000; 105
SELECT COUNT(*) FROM `characters` WHERE `deleteInfos_Account` <> '0' LIMIT 0, 1000; 7
need sql to move deleteInfos_Account, deleteInfos_Name, deleteDate to character_deleted

<Aokromes> SELECT COUNT(*) FROM item_instance; 3642391
<Aokromes> SELECT COUNT(*) FROM `item_instance` WHERE `enchantments` NOT LIKE '%1%' AND `enchantments` NOT LIKE '%2%' AND `enchantments` NOT LIKE '%3%' AND `enchantments` NOT LIKE '%4%' AND `enchantments` NOT LIKE '%5%' AND `enchantments` NOT LIKE '%6%' AND `enchantments` NOT LIKE '%7%' AND `enchantments` NOT LIKE '%8%' AND `enchantments` NOT LIKE '%9%' LIMIT 0, 1000
<Aokromes> 3395101
<Aokromes> 3.395.101 of 3.642.391 lacks enchantments

SELECT COUNT(*) FROM `item_instance` WHERE `enchantments` NOT LIKE '%0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0%' LIMIT 0, 1000
197196

--  diff day
<Aokromes> SELECT COUNT(*) FROM `item_instance` WHERE `text`  = ''; 2761449
<Aokromes> SELECT COUNT(*) FROM `item_instance` WHERE `text`  <> ''; 2068
<Aokromes> SELECT COUNT(*) FROM `item_instance` WHERE `playedTime` > '0'; 217539
<Aokromes> SELECT COUNT(*) FROM `item_instance` WHERE  `durability` > '0'; 828067
<Aokromes> SELECT COUNT(*) FROM `item_instance` WHERE   `randomPropertyId` > '0'; 81324
<Aokromes> SELECT COUNT(*) FROM `item_instance` WHERE  `flags` > '0'; 989091
<Aokromes> SELECT COUNT(*) FROM `item_instance` WHERE  `charges` NOT LIKE '%0 0 0 0 0%' LIMIT 0, 1000; 722083
<Aokromes> SELECT COUNT(*) FROM `item_instance` WHERE `charges` NOT LIKE '%0 0 0 0%' LIMIT 0, 1000; 1357
<Aokromes> we are using a blob for 1357 items of 2761449
<Aokromes> SELECT COUNT(*) FROM `item_instance` WHERE  `duration` > '0'; 11801
<Aokromes> SELECT COUNT(*) FROM `item_instance` WHERE `count` > '1' LIMIT 0, 1000; 1510186
<Aokromes> SELECT COUNT(*) FROM `item_instance` WHERE  `giftCreatorGuid` > '0'; 94872
<Aokromes> SELECT COUNT(*) FROM `item_instance` WHERE  `creatorGuid` > '0'; 376446
<Aokromes> SELECT COUNT(*) FROM `item_instance` WHERE  `creatorGuid` > '0' AND `count` = '1' ; 178179
<Aokromes> i wonder moving all those to separte tables how much db size will be decreased
<Aokromes> think that we can drop near 40 million of cells to become (300000*2 )+  (2068 * 2)+ (217539 *2) + (828067 * 2) + (81324*2) +(989091*2) + (722083*2) +(11801*2)+(1510186*2)+(94872*2)+( 376446*2) xd
<Aokromes> 40 millon of cels to 5.509.963 :P
<Aokromes> also, maybe we can unblob charges and enchantments and it can be even lower size

--- diff day
<Nyeriah> Aokromes: SELECT COUNT(*) FROM characters WHERE at_login <>0; 55898
<Aokromes> Nyeriah, of how much chars?
<Nyeriah> 194177
<Nyeriah> SELECT COUNT(*) FROM item_instance; 12529672
<Nyeriah> SELECT COUNT(*) FROM`item_instance` WHERE `enchantments` NOT LIKE '%1%' AND`enchantments` NOT LIKE '%2%' AND `enchantments` NOT LIKE '%3%' AND`enchantments` NOT LIKE '%4%' AND `enchantments` NOT LIKE '%5%' AND`enchantments` NOT LIKE '%6%' AND `enchantments` NOT LIKE '%7%' AND`enchantments` NOT LIKE '%8%' AND `enchantments` NOT LIKE '%9%' LIMIT 0, 1000; 11485781