API tools faq
paste
Aokromes

optimization v6

Aokromes
Jul 7th, 2015
314
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 14.91 KB | None | 0 0
raw download clone embed print report
  1. DROP TABLE IF EXISTS `characters_trans`;
  2. CREATE TABLE `characters_trans` (
  3. `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
  4. `trans_x` float NOT NULL DEFAULT '0',
  5. `trans_y` float NOT NULL DEFAULT '0',
  6. `trans_z` float NOT NULL DEFAULT '0',
  7. `trans_o` float NOT NULL DEFAULT '0',
  8. `transguid` mediumint(8) unsigned NOT NULL DEFAULT '0',
  9. PRIMARY KEY (`guid`)
  10. ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  11.  
  12. DROP TABLE IF EXISTS `characters_deleted`;
  13. CREATE TABLE `characters_deleted` (
  14. `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
  15. `deleteInfos_Account` int(10) unsigned DEFAULT NULL,
  16. `deleteInfos_Name` varchar(12) DEFAULT NULL,
  17. `deleteDate` int(10) unsigned DEFAULT NULL,
  18. PRIMARY KEY (`guid`)
  19. ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  20.  
  21. DROP TABLE IF EXISTS `characters_at_login`;
  22. CREATE TABLE `characters_at_login` (
  23. `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
  24. `at_login` smallint(5) unsigned NOT NULL DEFAULT '0',
  25. PRIMARY KEY (`guid`)
  26. ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  27.  
  28. DROP TABLE IF EXISTS `characters_drunk`;
  29. CREATE TABLE `characters_drunk` (
  30. `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
  31. `drunk` tinyint(3) unsigned NOT NULL DEFAULT '0',
  32. PRIMARY KEY (`guid`)
  33. ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  34.  
  35. DROP TABLE IF EXISTS `characters_grantableLevels`;
  36. CREATE TABLE `characters_grantableLevels` (
  37. `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
  38. `grantableLevels` tinyint(3) unsigned NOT NULL DEFAULT '0',
  39. PRIMARY KEY (`guid`)
  40. ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  41.  
  42. DROP TABLE IF EXISTS `characters_stable_slots`;
  43. CREATE TABLE `characters_stable_slots` (
  44. `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
  45. `stable_slots` tinyint(3) unsigned NOT NULL DEFAULT '0',
  46. PRIMARY KEY (`guid`)
  47. ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  48.  
  49. DROP TABLE IF EXISTS `characters_taxi_path`;
  50. CREATE TABLE `characters_taxi_path` (
  51. `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
  52. `taxi_path` text,
  53. PRIMARY KEY (`guid`)
  54. ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  55.  
  56. DROP TABLE IF EXISTS `characters_resettalents`;
  57. CREATE TABLE `characters_resettalents` (
  58. `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
  59. `resettalents_cost` int(10) unsigned NOT NULL DEFAULT '0',
  60. `resettalents_time` int(10) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`guid`)
  61. ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  62.  
  63. DROP TABLE IF EXISTS `characters_title`;
  64. CREATE TABLE `characters_title` (
  65. `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
  66. `chosenTitle` int(10) unsigned NOT NULL DEFAULT '0',
  67. `knownTitles` longtext,
  68. PRIMARY KEY (`guid`)
  69. ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  70.  
  71. DROP TABLE IF EXISTS `characters_pvp_stats`;
  72. CREATE TABLE `characters_pvp_stats` (
  73. `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
  74. `arenaPoints` int(10) unsigned NOT NULL DEFAULT '0',
  75. `totalHonorPoints` int(10) unsigned NOT NULL DEFAULT '0',
  76. `todayHonorPoints` int(10) unsigned NOT NULL DEFAULT '0',
  77. `yesterdayHonorPoints` int(10) unsigned NOT NULL DEFAULT '0',
  78. `totalKills` int(10) unsigned NOT NULL DEFAULT '0',
  79. `todayKills` smallint(5) unsigned NOT NULL DEFAULT '0',
  80. `yesterdayKills` smallint(5) unsigned NOT NULL DEFAULT '0'
  81. ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  82.  
  83. DROP TABLE IF EXISTS `item_instance_enchantments`;
  84. CREATE TABLE `item_instance_enchantments` (
  85. `guid` int(11) unsigned NOT NULL,
  86. `spell_1` int(10),
  87. `spell_2` int(10),
  88. `spell_3` int(10),
  89. `spell_4` int(10),
  90. `spell_5` int(10),
  91. `spell_6` int(10),
  92. `spell_7` int(10),
  93. `spell_8` int(10),
  94. `spell_9` int(10),
  95. `spell_10` int(10),
  96. `spell_11` int(10),
  97. `spell_12` int(10),
  98. `spell_13` int(10),
  99. `spell_14` int(10),
  100. `spell_15` int(10),
  101. `spell_16` int(10),
  102. `spell_17` int(10),
  103. `spell_18` int(10),
  104. `spell_19` int(10),
  105. `spell_20` int(10),
  106. `spell_21` int(10),
  107. `spell_22` int(10),
  108. `spell_23` int(10),
  109. `spell_24` int(10),
  110. `spell_25` int(10),
  111. `spell_26` int(10),
  112. `spell_27` int(10),
  113. `spell_28` int(10),
  114. `spell_29` int(10),
  115. `spell_30` int(10),
  116. `spell_31` int(10),
  117. `spell_32` int(10),
  118. `spell_33` int(10),
  119. `spell_34` int(10),
  120. `spell_35` int(10),
  121. `spell_36` int(10),
  122. PRIMARY KEY (`guid`)
  123. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  124.  
  125. DROP TABLE IF EXISTS `item_instance_charges`;
  126. CREATE TABLE `item_instance_charges` (
  127. `guid` int(11) unsigned NOT NULL,
  128. `charge_1` smallint(3),
  129. `charge_2` smallint(3),
  130. `charge_3` smallint(3),
  131. `charge_4` smallint(3),
  132. `charge_5` smallint(3),
  133. PRIMARY KEY (`guid`)
  134. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  135.  
  136. DROP TABLE IF EXISTS `item_instance_creator`;
  137. CREATE TABLE `item_instance_creator` (
  138. `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
  139. `creatorGuid` int(10) unsigned NOT NULL DEFAULT '0',
  140. `giftCreatorGuid` int(10) unsigned NOT NULL DEFAULT '0',
  141. PRIMARY KEY (`guid`)
  142. ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  143.  
  144. DROP TABLE IF EXISTS `item_instance_text`;
  145. CREATE TABLE `item_instance_text` (
  146. `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
  147. `text` text,
  148. PRIMARY KEY (`guid`)
  149. ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  150.  
  151. DROP TABLE IF EXISTS `item_instance_duration`;
  152. CREATE TABLE `item_instance_duration` (
  153. `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
  154. `duration` int(10) NOT NULL DEFAULT '0',
  155. PRIMARY KEY (`guid`)
  156. ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  157.  
  158. DROP TABLE IF EXISTS `item_instance_randomPropertyId`;
  159. CREATE TABLE `item_instance_randomPropertyId` (
  160. `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
  161. `randomPropertyId` smallint(5) NOT NULL DEFAULT '0',
  162. PRIMARY KEY (`guid`)
  163. ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  164.  
  165. DROP TABLE IF EXISTS `item_instance_played_time`;
  166. CREATE TABLE `item_instance_played_time` (
  167. `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
  168. `playedTime` int(10) NOT NULL DEFAULT '0',
  169. PRIMARY KEY (`guid`)
  170. ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  171.  
  172. INSERT INTO characters_trans (`guid`, `trans_x`, `trans_y`, `trans_z`, `trans_o`, `transguid`)
  173. (SELECT characters.guid, characters.trans_x, characters.trans_y, characters.trans_z, characters.trans_o, characters.transguid FROM characters WHERE characters.trans_x != 0);
  174.  
  175. INSERT INTO characters_deleted (`guid`, `deleteInfos_Account`, `deleteInfos_Name`, `deleteDate`)
  176. (SELECT characters.guid, characters.deleteInfos_Account, characters.deleteInfos_Name, characters.deleteDate FROM characters WHERE characters.deleteInfos_Account IS NOT NULL);
  177.  
  178. INSERT INTO characters_at_login (`guid`, `at_login`)
  179. (SELECT characters.guid, characters.at_login FROM characters WHERE characters.at_login >0);
  180.  
  181. INSERT INTO characters_drunk (`guid`, `drunk`)
  182. (SELECT characters.guid, characters.drunk FROM characters WHERE characters.drunk >0);
  183.  
  184. INSERT INTO characters_grantableLevels (`guid`, `grantableLevels`)
  185. (SELECT characters.guid, characters.grantableLevels FROM characters WHERE characters.grantableLevels >0);
  186.  
  187. INSERT INTO characters_stable_slots (`guid`, `stable_slots`)
  188. (SELECT characters.guid, characters.stable_slots FROM characters WHERE characters.stable_slots >0);
  189.  
  190. INSERT INTO characters_taxi_path (`guid`, `taxi_path`)
  191. (SELECT characters.guid, characters.taxi_path FROM characters WHERE characters.taxi_path <> '');
  192.  
  193. INSERT INTO characters_resettalents (`guid`, `resettalents_cost`, `resettalents_time`)
  194. (SELECT characters.guid, characters.resettalents_cost, characters.resettalents_time FROM characters WHERE characters.resettalents_cost >0);
  195.  
  196. INSERT INTO characters_title (`guid`, `chosenTitle`, `knownTitles`)
  197. (SELECT characters.guid, characters.chosenTitle, characters.knownTitles FROM characters WHERE characters.chosenTitle >0 OR characters.knownTitles <> "0 0 0 0 0 0");
  198.  
  199. INSERT INTO characters_pvp_stats (`guid`, `arenaPoints`,`totalHonorPoints`,`todayHonorPoints`,`yesterdayHonorPoints`,`totalKills`,`todayKills`,`yesterdayKills`)
  200. (SELECT characters.guid, characters.arenaPoints, characters.totalHonorPoints, characters.todayHonorPoints, characters.yesterdayHonorPoints, characters.totalKills, characters.todayKills, characters.yesterdayKills FROM characters WHERE characters.arenaPoints >0 OR characters.totalHonorPoints >0 OR todayHonorPoints >0 OR yesterdayHonorPoints >0 OR totalKills >0 OR todayKills >0 OR yesterdayKills >0);
  201.  
  202. INSERT INTO item_instance_text (`guid`, `text`)
  203. (SELECT item_instance.guid, item_instance.text FROM item_instance WHERE item_instance.text <> '');
  204.  
  205. INSERT INTO item_instance_creator (`guid`, `creatorGuid`, `giftCreatorGuid`)
  206. (SELECT item_instance.guid, item_instance.creatorGuid, item_instance.giftCreatorGuid FROM item_instance WHERE item_instance.creatorGuid !=0 or item_instance.giftCreatorGuid !=0);
  207.  
  208. INSERT INTO `item_instance_charges`
  209. SELECT `guid`,
  210. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`charges`), ' ', 1), ' ', -1) AS SIGNED)) AS `charge_1`,
  211. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`charges`), ' ', 2), ' ', -1) AS SIGNED)) AS `charge_2`,
  212. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`charges`), ' ', 3), ' ', -1) AS SIGNED)) AS `charge_3`,
  213. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`charges`), ' ', 4), ' ', -1) AS SIGNED)) AS `charge_4`,
  214. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`charges`), ' ', 5), ' ', -1) AS SIGNED)) AS `charge_5`
  215. FROM `item_instance` WHERE TRIM(`charges`) <> '0 0 0 0 0';
  216.  
  217. INSERT INTO `item_instance_enchantments`
  218. SELECT `guid`,
  219. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 1), ' ', -1) AS SIGNED)) AS `spell_1`,
  220. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 2), ' ', -1) AS SIGNED)) AS `spell_2`,
  221. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 3), ' ', -1) AS SIGNED)) AS `spell_3`,
  222. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 4), ' ', -1) AS SIGNED)) AS `spell_4`,
  223. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 5), ' ', -1) AS SIGNED)) AS `spell_5`,
  224. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 6), ' ', -1) AS SIGNED)) AS `spell_6`,
  225. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 7), ' ', -1) AS SIGNED)) AS `spell_7`,
  226. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 8), ' ', -1) AS SIGNED)) AS `spell_8`,
  227. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 9), ' ', -1) AS SIGNED)) AS `spell_9`,
  228. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 10), ' ', -1) AS SIGNED)) AS `spell_10`,
  229. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 11), ' ', -1) AS SIGNED)) AS `spell_11`,
  230. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 12), ' ', -1) AS SIGNED)) AS `spell_12`,
  231. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 13), ' ', -1) AS SIGNED)) AS `spell_13`,
  232. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 14), ' ', -1) AS SIGNED)) AS `spell_14`,
  233. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 15), ' ', -1) AS SIGNED)) AS `spell_15`,
  234. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 16), ' ', -1) AS SIGNED)) AS `spell_16`,
  235. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 17), ' ', -1) AS SIGNED)) AS `spell_17`,
  236. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 18), ' ', -1) AS SIGNED)) AS `spell_18`,
  237. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 19), ' ', -1) AS SIGNED)) AS `spell_19`,
  238. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 20), ' ', -1) AS SIGNED)) AS `spell_20`,
  239. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 21), ' ', -1) AS SIGNED)) AS `spell_21`,
  240. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 22), ' ', -1) AS SIGNED)) AS `spell_22`,
  241. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 23), ' ', -1) AS SIGNED)) AS `spell_23`,
  242. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 24), ' ', -1) AS SIGNED)) AS `spell_24`,
  243. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 25), ' ', -1) AS SIGNED)) AS `spell_25`,
  244. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 26), ' ', -1) AS SIGNED)) AS `spell_26`,
  245. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 27), ' ', -1) AS SIGNED)) AS `spell_27`,
  246. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 28), ' ', -1) AS SIGNED)) AS `spell_28`,
  247. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 29), ' ', -1) AS SIGNED)) AS `spell_29`,
  248. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 30), ' ', -1) AS SIGNED)) AS `spell_30`,
  249. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 31), ' ', -1) AS SIGNED)) AS `spell_31`,
  250. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 32), ' ', -1) AS SIGNED)) AS `spell_32`,
  251. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 33), ' ', -1) AS SIGNED)) AS `spell_33`,
  252. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 34), ' ', -1) AS SIGNED)) AS `spell_34`,
  253. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 35), ' ', -1) AS SIGNED)) AS `spell_35`,
  254. (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(`enchantments`), ' ', 36), ' ', -1) AS SIGNED)) AS `spell_36`
  255. FROM `item_instance` WHERE TRIM(`enchantments`) <> '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 0 0';
  256. INSERT INTO item_instance_duration (`guid`, `duration`)
  257. (SELECT item_instance.guid, item_instance.duration FROM item_instance WHERE item_instance.duration > 0);
  258.  
  259. INSERT INTO item_instance_randomPropertyId (`guid`, `randomPropertyId`)
  260. (SELECT item_instance.guid, item_instance.randomPropertyId FROM item_instance WHERE item_instance.randomPropertyId <> 0);
  261.  
  262. INSERT INTO item_instance_played_time (`guid`, `playedTime`)
  263. (SELECT item_instance.guid, item_instance.playedTime FROM item_instance WHERE item_instance.playedTime <> 0);
  264.  
  265. ALTER TABLE `characters` DROP `trans_x`, DROP `trans_y`, DROP `trans_z`, DROP `trans_o`, DROP `transguid`,
  266. DROP `deleteInfos_Account`, DROP `deleteInfos_Name`, DROP `deleteDate`,
  267. DROP `at_login`, DROP `drunk`, DROP `grantableLevels`,
  268. DROP `stable_slots`, DROP `taxi_path`,
  269. DROP `resettalents_time`, DROP `resettalents_cost`,
  270. DROP `arenaPoints`, DROP `totalHonorPoints`, DROP `todayHonorPoints`, DROP `yesterdayHonorPoints`,
  271. DROP `totalKills`, DROP `todayKills`, DROP `yesterdayKills`,
  272. DROP `chosenTitle`, DROP `knownTitles`;
  273. ALTER TABLE `item_instance` DROP `text`, DROP `charges`, DROP `enchantments`, DROP `duration`, DROP `randomPropertyId`, DROP `creatorGuid`, DROP `giftCreatorGuid`, DROP `playedTime`;
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!