From 6ce3e3bc0de4fe7faf2c7bed6e0b2d234b241f12 Mon Sep 17 00:00:00 2001 From: skotlex Date: Wed, 13 Sep 2006 01:07:45 +0000 Subject: - 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 --- src/login_sql/login.c | 25 +++++-------------------- 1 file changed, 5 insertions(+), 20 deletions(-) (limited to 'src/login_sql') 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); -- cgit v1.2.3-70-g09d2