From 3b0f46cd61c0398fbd52887f5db54da99aa4b150 Mon Sep 17 00:00:00 2001 From: skotlex Date: Mon, 23 Oct 2006 18:54:28 +0000 Subject: - Added a bunch of important indexes when dealing with the log tables. Added upgrade_svn9050.sql for this purpose. git-svn-id: https://rathena.svn.sourceforge.net/svnroot/rathena/trunk@9051 54d463be-8e91-2dee-dedb-b68131a5f0ec --- Changelog-Trunk.txt | 2 ++ sql-files/logs.sql | 22 ++++++++++++++++------ sql-files/upgrade_svn9050.sql | 6 ++++++ 3 files changed, 24 insertions(+), 6 deletions(-) create mode 100644 sql-files/upgrade_svn9050.sql diff --git a/Changelog-Trunk.txt b/Changelog-Trunk.txt index c0609c498..ff0c6f2c1 100644 --- a/Changelog-Trunk.txt +++ b/Changelog-Trunk.txt @@ -4,6 +4,8 @@ AS OF SVN REV. 5091, WE ARE NOW USING TRUNK. ALL UNTESTED BUGFIXES/FEATURES GO IF YOU HAVE A WORKING AND TESTED BUGFIX PUT IT INTO STABLE AS WELL AS TRUNK. 2006/10/23 + * Added a bunch of important indexes when dealing with the log tables. + Added upgrade_svn9050.sql which adds these indexes as well. [Skotlex] * Expanded the family check to work as it should, exp-share-range is respected, and the child is not considered for it (thanks to TheUltraMage for the investigation) [Skotlex] diff --git a/sql-files/logs.sql b/sql-files/logs.sql index aae128398..7a27f8a6a 100644 --- a/sql-files/logs.sql +++ b/sql-files/logs.sql @@ -17,7 +17,8 @@ CREATE TABLE `picklog` ( `card2` int(11) NOT NULL default '0', `card3` int(11) NOT NULL default '0', `map` varchar(20) NOT NULL default 'prontera.gat', - PRIMARY KEY (`id`) + PRIMARY KEY (`id`), + INDEX (`type`) ) TYPE=MyISAM AUTO_INCREMENT=1 ; #ZenyLog types (M)onsters,(T)rade,(V)ending Sell/Buy,(S)hop Sell/Buy,(N)PC Change amount,(A)dministrators @@ -31,7 +32,8 @@ CREATE TABLE `zenylog` ( `type` set('M','T','V','S','N','A') NOT NULL default 'S', `amount` int(11) NOT NULL default '0', `map` varchar(20) NOT NULL default 'prontera.gat', - PRIMARY KEY (`id`) + PRIMARY KEY (`id`), + INDEX (`type`) ) TYPE=MyISAM AUTO_INCREMENT=1 ; #Database: log @@ -43,7 +45,9 @@ CREATE TABLE `branchlog` ( `char_id` int(11) NOT NULL default '0', `char_name` varchar(30) NOT NULL default '', `map` varchar(20) NOT NULL default 'prontera.gat', - PRIMARY KEY (`branch_id`) + PRIMARY KEY (`branch_id`), + INDEX (`account_id`), + INDEX (`char_id`) ) TYPE=MyISAM AUTO_INCREMENT=1 ; #Database: log @@ -69,7 +73,9 @@ CREATE TABLE `atcommandlog` ( `char_name` varchar(30) NOT NULL default '', `map` varchar(20) NOT NULL default 'prontera.gat', `command` varchar(50) NOT NULL default '', - PRIMARY KEY (`atcommand_id`) + PRIMARY KEY (`atcommand_id`), + INDEX (`account_id`), + INDEX (`char_id`) ) TYPE=MyISAM AUTO_INCREMENT=1 ; #Database: log @@ -82,7 +88,9 @@ CREATE TABLE `npclog` ( `char_name` varchar(30) NOT NULL default '', `map` varchar(20) NOT NULL default 'prontera.gat', `mes` varchar(255) NOT NULL default '', - PRIMARY KEY (`npc_id`) + PRIMARY KEY (`npc_id`), + INDEX (`account_id`), + INDEX (`char_id`) ) TYPE=MyISAM AUTO_INCREMENT=1 ; #ChatLOG @@ -98,5 +106,7 @@ CREATE TABLE `chatlog` ( `src_map_y` smallint(4) NOT NULL default '0', `dst_charname` varchar(25) NOT NULL default '', `message` varchar(150) NOT NULL default '', - PRIMARY KEY (`id`) + PRIMARY KEY (`id`), + INDEX (`src_accountid`), + INDEX (`src_charid`) ) TYPE=MyISAM AUTO_INCREMENT=1 ; diff --git a/sql-files/upgrade_svn9050.sql b/sql-files/upgrade_svn9050.sql new file mode 100644 index 000000000..932bc3b38 --- /dev/null +++ b/sql-files/upgrade_svn9050.sql @@ -0,0 +1,6 @@ +ALTER TABLE `picklog` ADD INDEX (`type`); +ALTER TABLE `zenylog` ADD INDEX (`type`); +ALTER TABLE `branchlog` ADD INDEX (`account_id`), ADD INDEX (`char_id`); +ALTER TABLE `atcommandlog` ADD INDEX (`account_id`), ADD INDEX (`char_id`); +ALTER TABLE `npclog` ADD INDEX (`account_id`), ADD INDEX (`char_id`); +ALTER TABLE `chatlog` ADD INDEX (`src_accountid`), ADD INDEX (`src_charid`); -- cgit v1.2.3-60-g2f50