- Modified the login SQL server so that case insensitive lookups use "where name = BINARY 'name'" instead of "where BINARY name = 'name'", since this way the name index should be used, and performance will no longer be heavily affected.

- Modified main.sql to add 4 missing indexes (thanks to ErkDog): char_id on the tables memo/friends, and online/name on the char table. Added upgrade_svn8728.sql to add these indexes to already existing tables.
- Changed back the default of case-sensitive to ON since it shouldn't be such a bad performance hog now.


git-svn-id: https://rathena.svn.sourceforge.net/svnroot/rathena/trunk@8728 54d463be-8e91-2dee-dedb-b68131a5f0ec

1 files changed, 7 insertions, 3 deletions

diff --git a/sql-files/main.sql b/sql-files/main.sql
index 2edb747e3..155542119 100644
--- a/sql-files/main.sql
+++ b/sql-files/main.sql
@@ -81,7 +81,9 @@ CREATE TABLE `char` (
   PRIMARY KEY  (`char_id`),

   KEY `account_id` (`account_id`),

   KEY `party_id` (`party_id`),

-  KEY `guild_id` (`guild_id`)

+  KEY `guild_id` (`guild_id`),

+  KEY `name` (`name`),

+  KEY `online` (`online`)

 ) TYPE=MyISAM AUTO_INCREMENT=150000; 

 

 --

@@ -113,7 +115,8 @@ DROP TABLE IF EXISTS `friends`;
 CREATE TABLE `friends` (

   `char_id` int(11) NOT NULL default '0',

   `friend_account` int(11) NOT NULL default '0',

-  `friend_id` int(11) NOT NULL default '0'

+  `friend_id` int(11) NOT NULL default '0',

+  KEY  `char_id` (`char_id`)

 ) TYPE=MyISAM;

 

 --

@@ -461,7 +464,8 @@ CREATE TABLE `memo` (
   `map` varchar(255) NOT NULL default '',

   `x` smallint(9) unsigned NOT NULL default '0',

   `y` smallint(9) unsigned NOT NULL default '0',

-  PRIMARY KEY  (`memo_id`)

+  PRIMARY KEY  (`memo_id`),

+  KEY `char_id` (`char_id`)

 ) TYPE=MyISAM;

 

 --