API tools faq
paste
Aokromes

characters table size optimization

Aokromes
Jul 2nd, 2015
296
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.26 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_pvp_stats`;
  64. CREATE TABLE `characters_pvp_stats` (
  65. `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
  66. `arenaPoints` int(10) unsigned NOT NULL DEFAULT '0',
  67. `totalHonorPoints` int(10) unsigned NOT NULL DEFAULT '0',
  68. `todayHonorPoints` int(10) unsigned NOT NULL DEFAULT '0',
  69. `yesterdayHonorPoints` int(10) unsigned NOT NULL DEFAULT '0',
  70. `totalKills` int(10) unsigned NOT NULL DEFAULT '0',
  71. `todayKills` smallint(5) unsigned NOT NULL DEFAULT '0',
  72. `yesterdayKills` smallint(5) unsigned NOT NULL DEFAULT '0'
  73. ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  74.  
  75. DROP TABLE IF EXISTS `item_instance_enchantments`;
  76. CREATE TABLE `item_instance_enchantments` (
  77. `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
  78. `enchantments` text NOT NULL,
  79. PRIMARY KEY (`guid`)
  80. ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  81.  
  82. DROP TABLE IF EXISTS `item_instance_charges`;
  83. CREATE TABLE `item_instance_charges` (
  84. `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
  85. `charges` tinytext,
  86. PRIMARY KEY (`guid`)
  87. ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  88.  
  89. DROP TABLE IF EXISTS `item_instance_text`;
  90. CREATE TABLE `item_instance_text` (
  91. `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
  92. `text` text,
  93. PRIMARY KEY (`guid`)
  94. ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  95.  
  96. DROP TABLE IF EXISTS `item_instance_duration`;
  97. CREATE TABLE `item_instance_duration` (
  98. `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
  99. `duration` int(10) NOT NULL DEFAULT '0',
  100. PRIMARY KEY (`guid`)
  101. ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  102.  
  103. DROP TABLE IF EXISTS `item_instance_randomPropertyId`;
  104. CREATE TABLE `item_instance_randomPropertyId` (
  105. `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
  106. `randomPropertyId` smallint(5) NOT NULL DEFAULT '0',
  107. PRIMARY KEY (`guid`)
  108. ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  109.  
  110. INSERT INTO characters_trans (`guid`, `trans_x`, `trans_y`, `trans_z`, `trans_o`, `transguid`)
  111. (SELECT characters.guid, characters.trans_x, characters.trans_y, characters.trans_z, characters.trans_o, characters.transguid FROM characters WHERE characters.trans_x != 0);
  112.  
  113. INSERT INTO characters_deleted (`guid`, `deleteInfos_Account`, `deleteInfos_Name`, `deleteDate`)
  114. (SELECT characters.guid, characters.deleteInfos_Account, characters.deleteInfos_Name, characters.deleteDate FROM characters WHERE characters.deleteInfos_Account IS NOT NULL);
  115.  
  116. INSERT INTO characters_at_login (`guid`, `at_login`)
  117. (SELECT characters.guid, characters.at_login FROM characters WHERE characters.at_login >0);
  118.  
  119. INSERT INTO characters_drunk (`guid`, `drunk`)
  120. (SELECT characters.guid, characters.drunk FROM characters WHERE characters.drunk >0);
  121.  
  122. INSERT INTO characters_grantableLevels (`guid`, `grantableLevels`)
  123. (SELECT characters.guid, characters.grantableLevels FROM characters WHERE characters.grantableLevels >0);
  124.  
  125. INSERT INTO characters_stable_slots (`guid`, `stable_slots`)
  126. (SELECT characters.guid, characters.stable_slots FROM characters WHERE characters.stable_slots >0);
  127.  
  128. INSERT INTO characters_taxi_path (`guid`, `taxi_path`)
  129. (SELECT characters.guid, characters.taxi_path FROM characters WHERE characters.taxi_path <> '');
  130.  
  131. INSERT INTO characters_resettalents (`guid`, `resettalents_cost`, `resettalents_time`)
  132. (SELECT characters.guid, characters.resettalents_cost, characters.resettalents_time FROM characters WHERE characters.resettalents_cost >0);
  133.  
  134. INSERT INTO characters_pvp_stats (`guid`, `arenaPoints`,`totalHonorPoints`,`todayHonorPoints`,`yesterdayHonorPoints`,`totalKills`,`todayKills`,`yesterdayKills`)
  135. (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);
  136.  
  137. INSERT INTO item_instance_text (`guid`, `text`)
  138. (SELECT item_instance.guid, item_instance.text FROM item_instance WHERE item_instance.text <> '');
  139.  
  140. INSERT INTO item_instance_charges (`guid`, `charges`)
  141. (SELECT item_instance.guid, item_instance.charges FROM item_instance WHERE item_instance.charges <> '0 0 0 0 0 ');
  142.  
  143. INSERT INTO item_instance_enchantments (`guid`, `enchantments`)
  144. (SELECT item_instance.guid, item_instance.enchantments FROM item_instance WHERE item_instance.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');
  145.  
  146. INSERT INTO item_instance_duration (`guid`, `duration`)
  147. (SELECT item_instance.guid, item_instance.duration FROM item_instance WHERE item_instance.duration > 0);
  148.  
  149. INSERT INTO item_instance_randomPropertyId (`guid`, `randomPropertyId`)
  150. (SELECT item_instance.guid, item_instance.randomPropertyId FROM item_instance WHERE item_instance.randomPropertyId <> 0);
  151.  
  152. ALTER TABLE `characters` DROP `trans_x`, DROP `trans_y`, DROP `trans_z`, DROP `trans_o`, DROP `transguid`,
  153. DROP `deleteInfos_Account`, DROP `deleteInfos_Name`, DROP `deleteDate`,
  154. DROP `at_login`, DROP `drunk`, DROP `grantableLevels`,
  155. DROP `stable_slots`, DROP `taxi_path`,
  156. DROP `resettalents_time`, DROP `resettalents_cost`,
  157. DROP `arenaPoints`, DROP `totalHonorPoints`, DROP `todayHonorPoints`, DROP `yesterdayHonorPoints`,
  158. DROP `totalKills`, DROP `todayKills`, DROP `yesterdayKills`;
  159. ALTER TABLE `item_instance` DROP `text`, DROP `charges`, DROP `enchantments`, DROP `duration`, DROP `randomPropertyId`;
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!