summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--Changelog-Trunk.txt8
-rw-r--r--conf-tmpl/Changelog.txt2
-rw-r--r--conf-tmpl/login_athena.conf5
-rw-r--r--sql-files/main.sql10
-rw-r--r--sql-files/upgrade_svn8728.sql4
-rw-r--r--src/login_sql/login.c25
6 files changed, 27 insertions, 27 deletions
diff --git a/Changelog-Trunk.txt b/Changelog-Trunk.txt
index 8e5358c99..6517c3a69 100644
--- a/Changelog-Trunk.txt
+++ b/Changelog-Trunk.txt
@@ -4,6 +4,14 @@ 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/09/12
+ * 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. [Skotlex]
+ * 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.
+ [Skotlex]
* Updated convert_engine.sql to use TYPE instead of ENGINE to be compatible
with Mysql 4 installations. [Skotlex]
* Moved the morph restrictions from skill_additional_effect to
diff --git a/conf-tmpl/Changelog.txt b/conf-tmpl/Changelog.txt
index aa1c380ea..4499d63cb 100644
--- a/conf-tmpl/Changelog.txt
+++ b/conf-tmpl/Changelog.txt
@@ -1,6 +1,8 @@
Date Added
2006/09/12
+ * Changed back the default of case-sensitive to ON since it shouldn't be
+ such a bad performance hog now. [Skotlex]
* case_sensitive is now off by default due to performance issues
(login_athena.conf). [Skotlex]
2006/09/11
diff --git a/conf-tmpl/login_athena.conf b/conf-tmpl/login_athena.conf
index beb6b6169..41995a2db 100644
--- a/conf-tmpl/login_athena.conf
+++ b/conf-tmpl/login_athena.conf
@@ -51,10 +51,7 @@ ladminallowip: all
console: off
// Are login's case sensitive (SQL only)?
-// WARNING: Note that turning this on will cause login queries to use BINARY
-// searches, which disable the table's index and cause severe performance
-// penalties on large tables.
-case_sensitive: off
+case_sensitive: on
// Gamemaster password, used with the @gm command to obtain GM commands (level of gm set with level_new_gm parameter).
// NOTICE: You should also change this one.
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;
--
diff --git a/sql-files/upgrade_svn8728.sql b/sql-files/upgrade_svn8728.sql
new file mode 100644
index 000000000..71558893f
--- /dev/null
+++ b/sql-files/upgrade_svn8728.sql
@@ -0,0 +1,4 @@
+ALTER TABLE `friends` ADD INDEX ( `char_id` );
+ALTER TABLE `memo` ADD INDEX ( `char_id` );
+ALTER TABLE `char` ADD INDEX ( `name` );
+ALTER TABLE `char` ADD INDEX ( `online` );
diff --git a/src/login_sql/login.c b/src/login_sql/login.c
index d1f280c24..477e6c45a 100644
--- a/src/login_sql/login.c
+++ b/src/login_sql/login.c
@@ -640,7 +640,7 @@ int mmo_auth( struct mmo_account* account , int fd){
// make query
sprintf(tmpsql, "SELECT `%s`,`%s`,`%s`,`lastlogin`,`logincount`,`sex`,`connect_until`,`last_ip`,`ban_until`,`state`,`%s`"
- " FROM `%s` WHERE %s `%s`='%s'", login_db_account_id, login_db_userid, login_db_user_pass, login_db_level, login_db, case_sensitive ? "BINARY" : "", login_db_userid, t_uid);
+ " FROM `%s` WHERE `%s`= %s '%s'", login_db_account_id, login_db_userid, login_db_user_pass, login_db_level, login_db, login_db_userid, case_sensitive ? "BINARY" : "", t_uid);
//login {0-account_id/1-userid/2-user_pass/3-lastlogin/4-logincount/5-sex/6-connect_untl/7-last_ip/8-ban_until/9-state/10-level}
// query
@@ -790,26 +790,11 @@ int mmo_auth( struct mmo_account* account , int fd){
if (ban_until_time > time(NULL)) // always banned
return 6; // 6 = Your are Prohibited to log in until %s
- sprintf(tmpsql, "UPDATE `%s` SET `ban_until`='0' WHERE %s `%s`='%s'", login_db, case_sensitive ? "BINARY" : "", login_db_userid, t_uid);
+ sprintf(tmpsql, "UPDATE `%s` SET `ban_until`='0' WHERE `%s`= %s '%s'", login_db, login_db_userid, case_sensitive ? "BINARY" : "", t_uid);
if (mysql_query(&mysql_handle, tmpsql)) {
ShowSQL("DB error - %s\n",mysql_error(&mysql_handle));
ShowDebug("at %s:%d - %s\n", __FILE__,__LINE__,tmp_sql);
}
-
- // ban is finished
- // reset the ban time
-/* //Removed "state" of bans, it behaves now like their TXT counter-part. [Skotlex]
- if (atoi(sql_row[9])==7) {//it was a temp ban - so we set STATE to 0
- sprintf(tmpsql, "UPDATE `%s` SET `ban_until`='0', `state`='0' WHERE %s `%s`='%s'", login_db, case_sensitive ? "BINARY" : "", login_db_userid, t_uid);
- strcpy(sql_row[9],"0"); //we clear STATE
- } else //it was a permanent ban + temp ban. So we leave STATE = 5, but clear the temp ban
- sprintf(tmpsql, "UPDATE `%s` SET `ban_until`='0' WHERE %s `%s`='%s'", login_db, case_sensitive ? "BINARY" : "", login_db_userid, t_uid);
-
- if (mysql_query(&mysql_handle, tmpsql)) {
- ShowSQL("DB error - %s\n",mysql_error(&mysql_handle));
- ShowDebug("at %s:%d - %s\n", __FILE__,__LINE__,tmp_sql);
- }
-*/
}
if (atoi(sql_row[9])) {
@@ -875,8 +860,8 @@ int mmo_auth( struct mmo_account* account , int fd){
if (account->sex != 2 && account->account_id < START_ACCOUNT_NUM)
ShowWarning("Account %s has account id %d! Account IDs must be over %d to work properly!\n", account->userid, account->account_id, START_ACCOUNT_NUM);
- sprintf(tmpsql, "UPDATE `%s` SET `lastlogin` = NOW(), `logincount`=`logincount` +1, `last_ip`='%s' WHERE %s `%s` = '%s'",
- login_db, ip, case_sensitive ? "BINARY" : "", login_db_userid, sql_row[1]);
+ sprintf(tmpsql, "UPDATE `%s` SET `lastlogin` = NOW(), `logincount`=`logincount` +1, `last_ip`='%s' WHERE `%s` = %s '%s'",
+ login_db, ip, login_db_userid, case_sensitive ? "BINARY" : "", sql_row[1]);
mysql_free_result(sql_res) ; //resource free
if (mysql_query(&mysql_handle, tmpsql)) {
ShowSQL("DB error - %s\n",mysql_error(&mysql_handle));
@@ -1796,7 +1781,7 @@ int parse_login(int fd) {
//result = 5;
}
- sprintf(tmpsql,"SELECT `ban_until` FROM `%s` WHERE %s `%s` = '%s'",login_db, case_sensitive ? "BINARY" : "",login_db_userid, t_uid);
+ sprintf(tmpsql,"SELECT `ban_until` FROM `%s` WHERE `%s` = %s '%s'",login_db, login_db_userid, case_sensitive ? "BINARY" : "", t_uid);
if(mysql_query(&mysql_handle, tmpsql)) {
ShowSQL("DB error - %s\n",mysql_error(&mysql_handle));
ShowDebug("at %s:%d - %s\n", __FILE__,__LINE__,tmp_sql);
generated by cgit v1.2.3-70-g09d2 (git 2.46.0) at 2024-11-02 16:35:03 +0000