AnnieRuru

6. How to show the current rank of the player
Question : This is the part of the script, output as below
.@nb = query_sql("SELECT `name`, `kills` FROM `pvpladder` ORDER BY `kills` DESC LIMIT 5", .@name$, .@kills); for ( .@i = 0; .@i < .@nb; .@i++ ) mes "No."+(.@i+1)+" ["+ .@name$[.@i] +"] ~ "+ .@kills[.@i] +" kills"; No.1 [Alice] ~ 19 kills No.2 [Emily] ~ 11 kills No.3 [Irene] ~ 11 kills No.4 [Brittany] ~ 8 kills No.5 [Fiona] ~ 7 kills  2nd place and 3rd place has the same amount of kills, how do I make both of them display as 2nd place like this ?
No.1 [Alice] ~ 19 kills No.2 [Emily] ~ 11 kills No.2 [Irene] ~ 11 kills No.4 [Brittany] ~ 8 kills No.5 [Fiona] ~ 7 kills  
Answer :
Method no.1: Convert the table into InnoDB will return the result faster. Allow to use OFFSET
.@nb = query_sql("SELECT `name`, `kills`, FIND_IN_SET(`kills`, (SELECT GROUP_CONCAT(`kills` ORDER BY `kills` DESC) FROM `pvpladder`)) FROM `pvpladder` ORDER BY `kills` DESC LIMIT 5", .@name$, .@kills, .@rank); for ( .@i = 0; .@i < .@nb; ++.@i ) mes "No."+ .@rank[.@i] +" ["+ .@name$[.@i] +"] ~ "+ .@kills[.@i] +" kills"; Method no.2: This method return result faster than method 1 in huge table. Not allow to use OFFSET
.@query$ = "SELECT `name`, IF(@d=t.`kills`, @r, @r:=@i), @d:=t.`kills`, @i:=@i+1 "; .@query$ += "FROM `pvpladder` t, (SELECT @d:=0, @r:=0, @i:=1)q "; .@query$ += "ORDER BY `kills` DESC LIMIT 5"; .@nb = query_sql(.@query$, .@name$, .@rank, .@kills, .@dummy); for ( .@i = 0; .@i < .@nb; ++.@i ) mes "No."+ .@rank[.@i] +" ["+ .@name$[.@i] +"] ~ "+ .@kills[.@i] +" kills"; .
.
Question : How do I show the current ranking of the player ?
mes "Your kills -> "+ .@kills; mes "Your rank -> "+ .@rank; Answer :
query_sql "SELECT `kills`, 1+(SELECT COUNT(1) FROM `pvpladder` t1 WHERE t1.`kills` > t2.`kills`) FROM `pvpladder` t2 WHERE `char_id` = "+ getcharid(0), .@kills, .@rank; Remember to index the `kills` field
 
Reference : https://dba.stackexchange.com/questions/13703/get-the-rank-of-a-user-in-a-score-table
.
 

5. How to do IF-ELSE in SQL query ?
Question : I have a PVP ladder script that runs on Points system. Each kill plus 1 point and each death minus 1 point.
The problem is, this query will make the points go into negative value if the player is being kill repeatedly
query_sql "UPDATE `pvp_points` SET `points` = `points` - 1 WHERE `char_id` = "+ getcharid(0); How do I make the points stop at 0 if the player is already at 0 points ?
 
Answer :
query_sql "UPDATE `pvp_points` SET `points` = IF(`points` = 0, 0, `points` - 1) WHERE `char_id` = "+ getcharid(0); query_sql "UPDATE `pvp_points` SET `points` = (CASE WHEN `points` = 0 THEN 0 ELSE `points` - 1 END) WHERE `char_id` = "+ getcharid(0);  
Explanations:
similar to hercules script language,
if (<condition>) <execute true condition>; else <execute false condition>; in SQL language
IF(<condition>, <execute true condition>, <execute false condition>) CASE WHEN <condition> THEN <execute true condition> ELSE <execute false condition> END  
Reference : https://www.w3schools.com/sql/func_mysql_if.asp
https://stackoverflow.com/questions/63447/how-do-i-perform-an-if-then-in-an-sql-select
5a. How to update multiple rows on different conditions in a single query
This query will update multiple rows on different condition
UPDATE `pvpladder` SET `points` = CASE WHEN `char_id` = 150000 THEN `points` +1 WHEN `char_id` = 150001 THEN `points` -1 END WHERE `char_id` IN (150000,150001); Reference : https://stackoverflow.com/questions/20255138/sql-update-multiple-records-in-one-query

Personally I prefer it on top lol

Depending on how much you want DIY on the client, the learning curve can be steep.
Imho the primary skills are knowing how the client works from the player perspective (i.e. the whole functionality available, without modifying it) and knowing the subject you want to include/exclude. Thus, if you want to deal with bots, you should also know how bots work and be able to setup and use one.
Further down, you have to deal with C++, machine code generated from C++ (assembler), disassembling, debugging and Win32 API. If you stumble upon graphics stuff, GDI and DirectX 7 are also topics of interest. If you deal with network code and packets, some background knowledge of TCP is also helpful.
Note, that some things are better to be done server-side rather than client-side for two reasons:
Players can undo your client-side changes, but not server-side. Server-side is easier to edit, since you have all the source, whereas in the client you have only the gory machine code.

4. AUTO_INCREMENT
CREATE TABLE `support_ticket` ( `id` INT(11) AUTO_INCREMENT, `title` VARCHAR(70), `message` VARCHAR(255), PRIMARY KEY (`id`) ) ENGINE = InnoDB; In this kind of query that has AUTO_INCREMENT, many people do ....
$support_ticket_id++; query_sql "INSERT INTO `support_ticket` VALUES ( "+ $support_ticket_id +", '"+ escape_sql(.@title$) ... can be optimize .... using NULL
query_sql "INSERT INTO `support_ticket` VALUES ( NULL, '"+ escape_sql(.@title$) ... can retrieve the last row with
query_sql "SELECT MAX(`id`) FROM `support_ticket`", .@id; // ----- OR ----- query_sql "SELECT LAST_INSERT_ID()", .@id;  
Question : This question was asked on eathena forum board
One of my friend touched my custom table and the AUTO_INCREMENT has jump off the value
| 1 | <data set 1> | 2 | <data set 2> | 3 | <data set 3> | 25854 | <data set 4> | 25855 | <data set 5> | 25856 | <data set 6> I want to make the value return back to normal as shown
| 1 | <data set 1> | 2 | <data set 2> | 3 | <data set 3> | 4 | <data set 4> | 5 | <data set 5> | 6 | <data set 6> How to do this WITHOUT losing any of the current data ?
Answer: The trick is ... just drop that column and rebuild it
ALTER TABLE `inventory` DROP COLUMN `id`; ALTER TABLE `inventory` ADD COLUMN `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; convert the table into MyISAM will process the query much faster

6. How to show the current rank of the player
Question : This is the part of the script, output as below
.@nb = query_sql("SELECT `name`, `kills` FROM `pvpladder` ORDER BY `kills` DESC LIMIT 5", .@name$, .@kills); for ( .@i = 0; .@i < .@nb; .@i++ ) mes "No."+(.@i+1)+" ["+ .@name$[.@i] +"] ~ "+ .@kills[.@i] +" kills"; No.1 [Alice] ~ 19 kills No.2 [Emily] ~ 11 kills No.3 [Irene] ~ 11 kills No.4 [Brittany] ~ 8 kills No.5 [Fiona] ~ 7 kills  2nd place and 3rd place has the same amount of kills, how do I make both of them display as 2nd place like this ?
No.1 [Alice] ~ 19 kills No.2 [Emily] ~ 11 kills No.2 [Irene] ~ 11 kills No.4 [Brittany] ~ 8 kills No.5 [Fiona] ~ 7 kills  
Answer :
Method no.1: Convert the table into InnoDB will return the result faster. Allow to use OFFSET
.@nb = query_sql("SELECT `name`, `kills`, FIND_IN_SET(`kills`, (SELECT GROUP_CONCAT(`kills` ORDER BY `kills` DESC) FROM `pvpladder`)) FROM `pvpladder` ORDER BY `kills` DESC LIMIT 5", .@name$, .@kills, .@rank); for ( .@i = 0; .@i < .@nb; ++.@i ) mes "No."+ .@rank[.@i] +" ["+ .@name$[.@i] +"] ~ "+ .@kills[.@i] +" kills"; Method no.2: This method return result faster than method 1 in huge table. Not allow to use OFFSET
.@query$ = "SELECT `name`, IF(@d=t.`kills`, @r, @r:=@i), @d:=t.`kills`, @i:=@i+1 "; .@query$ += "FROM `pvpladder` t, (SELECT @d:=0, @r:=0, @i:=1)q "; .@query$ += "ORDER BY `kills` DESC LIMIT 5"; .@nb = query_sql(.@query$, .@name$, .@rank, .@kills, .@dummy); for ( .@i = 0; .@i < .@nb; ++.@i ) mes "No."+ .@rank[.@i] +" ["+ .@name$[.@i] +"] ~ "+ .@kills[.@i] +" kills"; .
.
Question : How do I show the current ranking of the player ?
mes "Your kills -> "+ .@kills; mes "Your rank -> "+ .@rank; Answer :
query_sql "SELECT `kills`, 1+(SELECT COUNT(1) FROM `pvpladder` t1 WHERE t1.`kills` > t2.`kills`) FROM `pvpladder` t2 WHERE `char_id` = "+ getcharid(0), .@kills, .@rank; Remember to index the `kills` field
 
Reference : https://dba.stackexchange.com/questions/13703/get-the-rank-of-a-user-in-a-score-table
.
 

As usual, I only write advance guides
This guide is a compilation of SQL commands that I have used, or Questions answered on the forum
every single subject here are related to Hercules/Ragnarok Online in some ways, so you won't feel bored reading them XD
Table of Content
1. When to use *escape_sql script command
2. How to build a case-sensitive table
3. Choose a table type, MyISAM or InnoDB ?
3a. How to index a table properly
3b. Why you shouldn't use `char_reg_num_db` table
4. AUTO_INCREMENT
5. How to do IF-ELSE in SQL query ?
5a. How to update multiple rows on different conditions in a single query
6. How to show the current rank of the player
7. INSERT INTO ... SELECT ...
8. Table JOIN vs AS
9. What is the maximum string limit for *query_sql
9a. UNION
This topic is now open to Suggestions, Ideas, Improvements, and Questions ~
I'm sure many of you have some questions since the creation of this topic
You may also post up your tricks if you want to share with us

1. When to use escape_sql script command
 
.
input .@haha$; dispbottom .@haha$; dispbottom escape_sql(.@haha$); it doesn't has much differences, because it only affect 3 special characters
' <- single quotation mark
" <- double quotation mark
\ <- left slash
if I input -> haha"lala'hehe <-
it will return -> haha\"lala\'hehe <-
this is what we call, Escape a character
in hercules script, we also know we can use " symbol in any string input
mes "Susan says :\" Today I ate 3 eggs \"."; where in the game client, you can see the " symbol in the npc msg box
let's say I have a sql script like this
prontera,153,171,5 script Show Characters 1_F_MARIA,{ mes "input name, I'll show you all characters name it has on that player's account"; input .@name$; .@nb = query_sql("SELECT `char_id`, `name` FROM `char` WHERE `name` LIKE '"+ .@name$ +"'", .@cid, .@name$); if ( !.@nb ) { mes "no result"; close; } for ( .@i = 0; .@i < .@nb; ++.@i ) mes .@cid[.@i] +" "+ .@name$[.@i]; close; } this script has a possibility to be hacked
because to perform sql injection, I can enclose the string with quotation mark, then use another sql command to hack
BUT with an escape_sql command, if the user want to enclose the string with quotation mark to hack the script
the escape_sql command escaped the string, the quotation mark the user input will be escaped
thus the script will become impossible to hack
just now that script was for string input
prontera,153,171,5 script Show Characters 1_F_MARIA,{ mes "input account ID, I'll show you all characters name it has on that player's account"; input .@aid$; .@nb = query_sql("SELECT `char_id`, `name` FROM `char` WHERE `account_id` = "+ escape_sql(.@aid$), .@cid, .@name$); if ( !.@nb ) { mes "no result"; close; } for ( .@i = 0; .@i < .@nb; ++.@i ) mes .@cid[.@i] +" "+ .@name$[.@i]; close; } this is another stupid case.
1. the scripter use string input while the script just needed a number
2. even with escape_sql command over there, there is no quotation mark at all
yes this script also has a risk to be hack
because escape_sql only escape quotation mark.
that hacker don't even have to input quotation mark because it is a number
and an injection query can be sent without any quotation mark input
there are 2 ways to solve this
either use numeric variable for the input command
or enclose that ....
..... WHERE `account_id` = '"+ escape_sql(.@aid$) +"'", .... with single quotation mark, when the hacker input a quotation mark will be escaped by escape_sql command
Reference : https://www.w3schools.com/sql/sql_injection.asp
escape_sql command for another thing is
if the player register their names containing ' or ", these characters are escaped
only happens when the server have no restriction on the creation of players name
hercules\conf\char\char-server.conf
// Manage possible letters/symbol in the name of charater. Control character (0x00-0x1f) are never accepted. Possible values are: // NOTE: Applies to character, party and guild names. // 0: no restriction (default) // 1: only letters/symbols in 'name_letters' option. // 2: Letters/symbols in 'name_letters' option are forbidden. All others are possibles. name_option: 1  
and this was what happened to my SQL dota pvpladder script
Silo's Babies <-- this is a guild name
you can see the 5th string has a single quotation mark
with escape_sql command, that string will turn into
Silo\'s Babies <-- the quotation mark is escaped when send to sql query

2. How to build a case-sensitive table
this is the answer I found
http://dba.stackexchange.com/questions/15250/how-to-do-a-case-sensitive-search-in-where-clause
by default, the table creation use charset = latin1;
means it couldn't do a case-sensitive search
if you want to do a case-sensitive in a query, use BINARY
SELECT * FROM `char` WHERE `name` = BINARY('AnnieRuru'); however using BINARY might have performance hit if it is a big table
so its more recommend to convert your SQL table to collate with latin1_general_cs

let's say this is a sample table
CREATE TABLE `test` ( `id` INT(11) PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(23) )ENGINE = InnoDB; do an ALTER table syntax
ALTER TABLE `test` MODIFY COLUMN `name` VARCHAR(23) COLLATE latin1_general_cs; or just put it into the table creation
CREATE TABLE `test` ( `id` INT(11) PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(23) )ENGINE = InnoDB DEFAULT CHARSET = latin1 COLLATE latin1_general_cs;  

3. Choose a table type, MyISAM or InnoDB ?
https://stackoverflow.com/questions/20148/myisam-versus-innodb
Before MySQL 5.5,
MyISAM is mostly use for read-heavy + table locking storage engine = such as pvp ladder ( always select ... order by kill desc )
InnoDB is mostly use for write-heavy + row locking storage engine = such as quest script ( select ... from char_id ... only 1 row is retrieve )
After MySQL 5.6, (currently is 8.0)
just stick to InnoDB
there is only 1 reason MyISAM is better than InnoDB
- MyISAM use smaller disk usage than InnoDB
let's take a look at our MyISAM to InnoDB converter
https://github.com/HerculesWS/Hercules/blob/stable/sql-files/tools/convert_engine_innodb.sql
This converter is useful if you are using MySQL 5.6 or above
There are 4 tables that are commented out
the reason is simple, these 4 tables only read once and forgotten when server is live
since MyISAM is good at reading (SELECT) + smaller disk usage, its no use to convert these 4 tables into InnoDB
3a How to index a table properly
http://mysql.rjweb.org/doc.php/index_cookbook_mysql
http://www.dbta.com/Columns/DBA-Corner/Top-10-Steps-to-Building-Useful-Database-Indexes-100498.aspx
a simple thumb of rule, anything that is SELECT .... WHERE `field` = .....
that `field` has to be index
let's take a look at this PVP Ladder script that use Kill/Death ratio
CREATE TABLE `pvpladder` ( `char_id` INT(11), `name` VARCHAR(23), `kills` INT(11), `death` INT(11), PRIMARY KEY (`char_id`), KEY (`kills`, `death`) ) ENGINE = InnoDB; prontera,155,186,6 script PVP Ladder 1_F_MARIA,{ .@nb = query_sql( "SELECT `name`, `kills`/(`death`+1) FROM `pvpladder` WHERE `kills` > 0 ORDER BY `kills`/(`death`+1) DESC LIMIT 10", .@name$, .@ratio$ ); if ( !.@nb ) { mes "no entry"; close; } mes "Current Ranking :"; for ( .@i = 0; .@i < .@nb; ++.@i ) mes "No."+(.@i +1)+" ["+ .@name$[.@i] +"] "+ .@ratio$[.@i] +" kill"; close; OnPCKillEvent: if ( killedrid == getcharid(3) ) { // killing self should only increase death count. EG: Grand-cross query_sql "INSERT INTO `pvpladder` VALUES ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) )+"', 0,1 ) ON DUPLICATE KEY UPDATE `death` = `death` +1"; end; } query_sql "INSERT INTO `pvpladder` VALUES ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) )+"', 1,0 ) ON DUPLICATE KEY UPDATE `kills` = `kills` +1"; attachrid killedrid; query_sql "INSERT INTO `pvpladder` VALUES ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) )+"', 0,1 ) ON DUPLICATE KEY UPDATE `death` = `death` +1"; end; } This kind of query -> ORDER BY kills/death, needs to index them together like this
KEY (`kills`, `death`) 3b. Why you shouldn't use `char_reg_num_db` table
blame Euphy for spreading this technique
There are 2 reasons why you shouldn't even touch all these variable tables
Reason no.1 This table is sorely meant for server usage
Once these data is loaded, it is process internally, and only save character data according to this configuration
Reason no.2 The `value` field is not index !
This line has ORDER BY `value`, try recheck our main.sql file
CREATE TABLE IF NOT EXISTS `acc_reg_num_db` ( `account_id` INT(11) UNSIGNED NOT NULL DEFAULT '0', `key` VARCHAR(32) BINARY NOT NULL DEFAULT '', `index` INT(11) UNSIGNED NOT NULL DEFAULT '0', `value` INT(11) NOT NULL DEFAULT '0', PRIMARY KEY (`account_id`,`key`,`index`), KEY `account_id` (`account_id`) ) ENGINE=MyISAM; SQL will search through every single line in the `value` field if that column isn't index
Of course you can ... do ALTER table to add KEY to the `value` field
but this table has already optimized in that way for server usage
the more field you index into the table, the more disk usage space it use
Conclusion : If you want to make a custom script, then make a custom table. Leave these table alone !

4. AUTO_INCREMENT
CREATE TABLE `support_ticket` ( `id` INT(11) AUTO_INCREMENT, `title` VARCHAR(70), `message` VARCHAR(255), PRIMARY KEY (`id`) ) ENGINE = InnoDB; In this kind of query that has AUTO_INCREMENT, many people do ....
$support_ticket_id++; query_sql "INSERT INTO `support_ticket` VALUES ( "+ $support_ticket_id +", '"+ escape_sql(.@title$) ... can be optimize .... using NULL
query_sql "INSERT INTO `support_ticket` VALUES ( NULL, '"+ escape_sql(.@title$) ... can retrieve the last row with
query_sql "SELECT MAX(`id`) FROM `support_ticket`", .@id; // ----- OR ----- query_sql "SELECT LAST_INSERT_ID()", .@id;  
Question : This question was asked on eathena forum board
One of my friend touched my custom table and the AUTO_INCREMENT has jump off the value
| 1 | <data set 1> | 2 | <data set 2> | 3 | <data set 3> | 25854 | <data set 4> | 25855 | <data set 5> | 25856 | <data set 6> I want to make the value return back to normal as shown
| 1 | <data set 1> | 2 | <data set 2> | 3 | <data set 3> | 4 | <data set 4> | 5 | <data set 5> | 6 | <data set 6> How to do this WITHOUT losing any of the current data ?
Answer: The trick is ... just drop that column and rebuild it
ALTER TABLE `inventory` DROP COLUMN `id`; ALTER TABLE `inventory` ADD COLUMN `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; convert the table into MyISAM will process the query much faster

5. How to do IF-ELSE in SQL query ?
Question : I have a PVP ladder script that runs on Points system. Each kill plus 1 point and each death minus 1 point.
The problem is, this query will make the points go into negative value if the player is being kill repeatedly
query_sql "UPDATE `pvp_points` SET `points` = `points` - 1 WHERE `char_id` = "+ getcharid(0); How do I make the points stop at 0 if the player is already at 0 points ?
 
Answer :
query_sql "UPDATE `pvp_points` SET `points` = IF(`points` = 0, 0, `points` - 1) WHERE `char_id` = "+ getcharid(0); query_sql "UPDATE `pvp_points` SET `points` = (CASE WHEN `points` = 0 THEN 0 ELSE `points` - 1 END) WHERE `char_id` = "+ getcharid(0);  
Explanations:
similar to hercules script language,
if (<condition>) <execute true condition>; else <execute false condition>; in SQL language
IF(<condition>, <execute true condition>, <execute false condition>) CASE WHEN <condition> THEN <execute true condition> ELSE <execute false condition> END  
Reference : https://www.w3schools.com/sql/func_mysql_if.asp
https://stackoverflow.com/questions/63447/how-do-i-perform-an-if-then-in-an-sql-select
5a. How to update multiple rows on different conditions in a single query
This query will update multiple rows on different condition
UPDATE `pvpladder` SET `points` = CASE WHEN `char_id` = 150000 THEN `points` +1 WHEN `char_id` = 150001 THEN `points` -1 END WHERE `char_id` IN (150000,150001); Reference : https://stackoverflow.com/questions/20255138/sql-update-multiple-records-in-one-query

6. How to show the current rank of the player
Question : This is the part of the script, output as below
.@nb = query_sql("SELECT `name`, `kills` FROM `pvpladder` ORDER BY `kills` DESC LIMIT 5", .@name$, .@kills); for ( .@i = 0; .@i < .@nb; .@i++ ) mes "No."+(.@i+1)+" ["+ .@name$[.@i] +"] ~ "+ .@kills[.@i] +" kills"; No.1 [Alice] ~ 19 kills No.2 [Emily] ~ 11 kills No.3 [Irene] ~ 11 kills No.4 [Brittany] ~ 8 kills No.5 [Fiona] ~ 7 kills  2nd place and 3rd place has the same amount of kills, how do I make both of them display as 2nd place like this ?
No.1 [Alice] ~ 19 kills No.2 [Emily] ~ 11 kills No.2 [Irene] ~ 11 kills No.4 [Brittany] ~ 8 kills No.5 [Fiona] ~ 7 kills  
Answer :
Method no.1: Convert the table into InnoDB will return the result faster. Allow to use OFFSET
.@nb = query_sql("SELECT `name`, `kills`, FIND_IN_SET(`kills`, (SELECT GROUP_CONCAT(`kills` ORDER BY `kills` DESC) FROM `pvpladder`)) FROM `pvpladder` ORDER BY `kills` DESC LIMIT 5", .@name$, .@kills, .@rank); for ( .@i = 0; .@i < .@nb; ++.@i ) mes "No."+ .@rank[.@i] +" ["+ .@name$[.@i] +"] ~ "+ .@kills[.@i] +" kills"; Method no.2: This method return result faster than method 1 in huge table. Not allow to use OFFSET
.@query$ = "SELECT `name`, IF(@d=t.`kills`, @r, @r:=@i), @d:=t.`kills`, @i:=@i+1 "; .@query$ += "FROM `pvpladder` t, (SELECT @d:=0, @r:=0, @i:=1)q "; .@query$ += "ORDER BY `kills` DESC LIMIT 5"; .@nb = query_sql(.@query$, .@name$, .@rank, .@kills, .@dummy); for ( .@i = 0; .@i < .@nb; ++.@i ) mes "No."+ .@rank[.@i] +" ["+ .@name$[.@i] +"] ~ "+ .@kills[.@i] +" kills"; .
.
Question : How do I show the current ranking of the player ?
mes "Your kills -> "+ .@kills; mes "Your rank -> "+ .@rank; Answer :
query_sql "SELECT `kills`, 1+(SELECT COUNT(1) FROM `pvpladder` t1 WHERE t1.`kills` > t2.`kills`) FROM `pvpladder` t2 WHERE `char_id` = "+ getcharid(0), .@kills, .@rank; Remember to index the `kills` field
 
Reference : https://dba.stackexchange.com/questions/13703/get-the-rank-of-a-user-in-a-score-table
.
 

1. When to use escape_sql script command
 
.
input .@haha$; dispbottom .@haha$; dispbottom escape_sql(.@haha$); it doesn't has much differences, because it only affect 3 special characters
' <- single quotation mark
" <- double quotation mark
\ <- left slash
if I input -> haha"lala'hehe <-
it will return -> haha\"lala\'hehe <-
this is what we call, Escape a character
in hercules script, we also know we can use " symbol in any string input
mes "Susan says :\" Today I ate 3 eggs \"."; where in the game client, you can see the " symbol in the npc msg box
let's say I have a sql script like this
prontera,153,171,5 script Show Characters 1_F_MARIA,{ mes "input name, I'll show you all characters name it has on that player's account"; input .@name$; .@nb = query_sql("SELECT `char_id`, `name` FROM `char` WHERE `name` LIKE '"+ .@name$ +"'", .@cid, .@name$); if ( !.@nb ) { mes "no result"; close; } for ( .@i = 0; .@i < .@nb; ++.@i ) mes .@cid[.@i] +" "+ .@name$[.@i]; close; } this script has a possibility to be hacked
because to perform sql injection, I can enclose the string with quotation mark, then use another sql command to hack
BUT with an escape_sql command, if the user want to enclose the string with quotation mark to hack the script
the escape_sql command escaped the string, the quotation mark the user input will be escaped
thus the script will become impossible to hack
just now that script was for string input
prontera,153,171,5 script Show Characters 1_F_MARIA,{ mes "input account ID, I'll show you all characters name it has on that player's account"; input .@aid$; .@nb = query_sql("SELECT `char_id`, `name` FROM `char` WHERE `account_id` = "+ escape_sql(.@aid$), .@cid, .@name$); if ( !.@nb ) { mes "no result"; close; } for ( .@i = 0; .@i < .@nb; ++.@i ) mes .@cid[.@i] +" "+ .@name$[.@i]; close; } this is another stupid case.
1. the scripter use string input while the script just needed a number
2. even with escape_sql command over there, there is no quotation mark at all
yes this script also has a risk to be hack
because escape_sql only escape quotation mark.
that hacker don't even have to input quotation mark because it is a number
and an injection query can be sent without any quotation mark input
there are 2 ways to solve this
either use numeric variable for the input command
or enclose that ....
..... WHERE `account_id` = '"+ escape_sql(.@aid$) +"'", .... with single quotation mark, when the hacker input a quotation mark will be escaped by escape_sql command
Reference : https://www.w3schools.com/sql/sql_injection.asp
escape_sql command for another thing is
if the player register their names containing ' or ", these characters are escaped
only happens when the server have no restriction on the creation of players name
hercules\conf\char\char-server.conf
// Manage possible letters/symbol in the name of charater. Control character (0x00-0x1f) are never accepted. Possible values are: // NOTE: Applies to character, party and guild names. // 0: no restriction (default) // 1: only letters/symbols in 'name_letters' option. // 2: Letters/symbols in 'name_letters' option are forbidden. All others are possibles. name_option: 1  
and this was what happened to my SQL dota pvpladder script
Silo's Babies <-- this is a guild name
you can see the 5th string has a single quotation mark
with escape_sql command, that string will turn into
Silo\'s Babies <-- the quotation mark is escaped when send to sql query

2. How to build a case-sensitive table
this is the answer I found
http://dba.stackexchange.com/questions/15250/how-to-do-a-case-sensitive-search-in-where-clause
by default, the table creation use charset = latin1;
means it couldn't do a case-sensitive search
if you want to do a case-sensitive in a query, use BINARY
SELECT * FROM `char` WHERE `name` = BINARY('AnnieRuru'); however using BINARY might have performance hit if it is a big table
so its more recommend to convert your SQL table to collate with latin1_general_cs

let's say this is a sample table
CREATE TABLE `test` ( `id` INT(11) PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(23) )ENGINE = InnoDB; do an ALTER table syntax
ALTER TABLE `test` MODIFY COLUMN `name` VARCHAR(23) COLLATE latin1_general_cs; or just put it into the table creation
CREATE TABLE `test` ( `id` INT(11) PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(23) )ENGINE = InnoDB DEFAULT CHARSET = latin1 COLLATE latin1_general_cs;  

3. Choose a table type, MyISAM or InnoDB ?
https://stackoverflow.com/questions/20148/myisam-versus-innodb
Before MySQL 5.5,
MyISAM is mostly use for read-heavy + table locking storage engine = such as pvp ladder ( always select ... order by kill desc )
InnoDB is mostly use for write-heavy + row locking storage engine = such as quest script ( select ... from char_id ... only 1 row is retrieve )
After MySQL 5.6, (currently is 8.0)
just stick to InnoDB
there is only 1 reason MyISAM is better than InnoDB
- MyISAM use smaller disk usage than InnoDB
let's take a look at our MyISAM to InnoDB converter
https://github.com/HerculesWS/Hercules/blob/stable/sql-files/tools/convert_engine_innodb.sql
This converter is useful if you are using MySQL 5.6 or above
There are 4 tables that are commented out
the reason is simple, these 4 tables only read once and forgotten when server is live
since MyISAM is good at reading (SELECT) + smaller disk usage, its no use to convert these 4 tables into InnoDB
3a How to index a table properly
http://mysql.rjweb.org/doc.php/index_cookbook_mysql
http://www.dbta.com/Columns/DBA-Corner/Top-10-Steps-to-Building-Useful-Database-Indexes-100498.aspx
a simple thumb of rule, anything that is SELECT .... WHERE `field` = .....
that `field` has to be index
let's take a look at this PVP Ladder script that use Kill/Death ratio
CREATE TABLE `pvpladder` ( `char_id` INT(11), `name` VARCHAR(23), `kills` INT(11), `death` INT(11), PRIMARY KEY (`char_id`), KEY (`kills`, `death`) ) ENGINE = InnoDB; prontera,155,186,6 script PVP Ladder 1_F_MARIA,{ .@nb = query_sql( "SELECT `name`, `kills`/(`death`+1) FROM `pvpladder` WHERE `kills` > 0 ORDER BY `kills`/(`death`+1) DESC LIMIT 10", .@name$, .@ratio$ ); if ( !.@nb ) { mes "no entry"; close; } mes "Current Ranking :"; for ( .@i = 0; .@i < .@nb; ++.@i ) mes "No."+(.@i +1)+" ["+ .@name$[.@i] +"] "+ .@ratio$[.@i] +" kill"; close; OnPCKillEvent: if ( killedrid == getcharid(3) ) { // killing self should only increase death count. EG: Grand-cross query_sql "INSERT INTO `pvpladder` VALUES ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) )+"', 0,1 ) ON DUPLICATE KEY UPDATE `death` = `death` +1"; end; } query_sql "INSERT INTO `pvpladder` VALUES ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) )+"', 1,0 ) ON DUPLICATE KEY UPDATE `kills` = `kills` +1"; attachrid killedrid; query_sql "INSERT INTO `pvpladder` VALUES ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) )+"', 0,1 ) ON DUPLICATE KEY UPDATE `death` = `death` +1"; end; } This kind of query -> ORDER BY kills/death, needs to index them together like this
KEY (`kills`, `death`) 3b. Why you shouldn't use `char_reg_num_db` table
blame Euphy for spreading this technique
There are 2 reasons why you shouldn't even touch all these variable tables
Reason no.1 This table is sorely meant for server usage
Once these data is loaded, it is process internally, and only save character data according to this configuration
Reason no.2 The `value` field is not index !
This line has ORDER BY `value`, try recheck our main.sql file
CREATE TABLE IF NOT EXISTS `acc_reg_num_db` ( `account_id` INT(11) UNSIGNED NOT NULL DEFAULT '0', `key` VARCHAR(32) BINARY NOT NULL DEFAULT '', `index` INT(11) UNSIGNED NOT NULL DEFAULT '0', `value` INT(11) NOT NULL DEFAULT '0', PRIMARY KEY (`account_id`,`key`,`index`), KEY `account_id` (`account_id`) ) ENGINE=MyISAM; SQL will search through every single line in the `value` field if that column isn't index
Of course you can ... do ALTER table to add KEY to the `value` field
but this table has already optimized in that way for server usage
the more field you index into the table, the more disk usage space it use
Conclusion : If you want to make a custom script, then make a custom table. Leave these table alone !

4. AUTO_INCREMENT
CREATE TABLE `support_ticket` ( `id` INT(11) AUTO_INCREMENT, `title` VARCHAR(70), `message` VARCHAR(255), PRIMARY KEY (`id`) ) ENGINE = InnoDB; In this kind of query that has AUTO_INCREMENT, many people do ....
$support_ticket_id++; query_sql "INSERT INTO `support_ticket` VALUES ( "+ $support_ticket_id +", '"+ escape_sql(.@title$) ... can be optimize .... using NULL
query_sql "INSERT INTO `support_ticket` VALUES ( NULL, '"+ escape_sql(.@title$) ... can retrieve the last row with
query_sql "SELECT MAX(`id`) FROM `support_ticket`", .@id; // ----- OR ----- query_sql "SELECT LAST_INSERT_ID()", .@id;  
Question : This question was asked on eathena forum board
One of my friend touched my custom table and the AUTO_INCREMENT has jump off the value
| 1 | <data set 1> | 2 | <data set 2> | 3 | <data set 3> | 25854 | <data set 4> | 25855 | <data set 5> | 25856 | <data set 6> I want to make the value return back to normal as shown
| 1 | <data set 1> | 2 | <data set 2> | 3 | <data set 3> | 4 | <data set 4> | 5 | <data set 5> | 6 | <data set 6> How to do this WITHOUT losing any of the current data ?
Answer: The trick is ... just drop that column and rebuild it
ALTER TABLE `inventory` DROP COLUMN `id`; ALTER TABLE `inventory` ADD COLUMN `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; convert the table into MyISAM will process the query much faster

5. How to do IF-ELSE in SQL query ?
Question : I have a PVP ladder script that runs on Points system. Each kill plus 1 point and each death minus 1 point.
The problem is, this query will make the points go into negative value if the player is being kill repeatedly
query_sql "UPDATE `pvp_points` SET `points` = `points` - 1 WHERE `char_id` = "+ getcharid(0); How do I make the points stop at 0 if the player is already at 0 points ?
 
Answer :
query_sql "UPDATE `pvp_points` SET `points` = IF(`points` = 0, 0, `points` - 1) WHERE `char_id` = "+ getcharid(0); query_sql "UPDATE `pvp_points` SET `points` = (CASE WHEN `points` = 0 THEN 0 ELSE `points` - 1 END) WHERE `char_id` = "+ getcharid(0);  
Explanations:
similar to hercules script language,
if (<condition>) <execute true condition>; else <execute false condition>; in SQL language
IF(<condition>, <execute true condition>, <execute false condition>) CASE WHEN <condition> THEN <execute true condition> ELSE <execute false condition> END  
Reference : https://www.w3schools.com/sql/func_mysql_if.asp
https://stackoverflow.com/questions/63447/how-do-i-perform-an-if-then-in-an-sql-select
5a. How to update multiple rows on different conditions in a single query
This query will update multiple rows on different condition
UPDATE `pvpladder` SET `points` = CASE WHEN `char_id` = 150000 THEN `points` +1 WHEN `char_id` = 150001 THEN `points` -1 END WHERE `char_id` IN (150000,150001); Reference : https://stackoverflow.com/questions/20255138/sql-update-multiple-records-in-one-query

5. How to do IF-ELSE in SQL query ?
Question : I have a PVP ladder script that runs on Points system. Each kill plus 1 point and each death minus 1 point.
The problem is, this query will make the points go into negative value if the player is being kill repeatedly
query_sql "UPDATE `pvp_points` SET `points` = `points` - 1 WHERE `char_id` = "+ getcharid(0); How do I make the points stop at 0 if the player is already at 0 points ?
 
Answer :
query_sql "UPDATE `pvp_points` SET `points` = IF(`points` = 0, 0, `points` - 1) WHERE `char_id` = "+ getcharid(0); query_sql "UPDATE `pvp_points` SET `points` = (CASE WHEN `points` = 0 THEN 0 ELSE `points` - 1 END) WHERE `char_id` = "+ getcharid(0);  
Explanations:
similar to hercules script language,
if (<condition>) <execute true condition>; else <execute false condition>; in SQL language
IF(<condition>, <execute true condition>, <execute false condition>) CASE WHEN <condition> THEN <execute true condition> ELSE <execute false condition> END  
Reference : https://www.w3schools.com/sql/func_mysql_if.asp
https://stackoverflow.com/questions/63447/how-do-i-perform-an-if-then-in-an-sql-select
5a. How to update multiple rows on different conditions in a single query
This query will update multiple rows on different condition
UPDATE `pvpladder` SET `points` = CASE WHEN `char_id` = 150000 THEN `points` +1 WHEN `char_id` = 150001 THEN `points` -1 END WHERE `char_id` IN (150000,150001); Reference : https://stackoverflow.com/questions/20255138/sql-update-multiple-records-in-one-query

4. AUTO_INCREMENT
CREATE TABLE `support_ticket` ( `id` INT(11) AUTO_INCREMENT, `title` VARCHAR(70), `message` VARCHAR(255), PRIMARY KEY (`id`) ) ENGINE = InnoDB; In this kind of query that has AUTO_INCREMENT, many people do ....
$support_ticket_id++; query_sql "INSERT INTO `support_ticket` VALUES ( "+ $support_ticket_id +", '"+ escape_sql(.@title$) ... can be optimize .... using NULL
query_sql "INSERT INTO `support_ticket` VALUES ( NULL, '"+ escape_sql(.@title$) ... can retrieve the last row with
query_sql "SELECT MAX(`id`) FROM `support_ticket`", .@id; // ----- OR ----- query_sql "SELECT LAST_INSERT_ID()", .@id;  
Question : This question was asked on eathena forum board
One of my friend touched my custom table and the AUTO_INCREMENT has jump off the value
| 1 | <data set 1> | 2 | <data set 2> | 3 | <data set 3> | 25854 | <data set 4> | 25855 | <data set 5> | 25856 | <data set 6> I want to make the value return back to normal as shown
| 1 | <data set 1> | 2 | <data set 2> | 3 | <data set 3> | 4 | <data set 4> | 5 | <data set 5> | 6 | <data set 6> How to do this WITHOUT losing any of the current data ?
Answer: The trick is ... just drop that column and rebuild it
ALTER TABLE `inventory` DROP COLUMN `id`; ALTER TABLE `inventory` ADD COLUMN `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; convert the table into MyISAM will process the query much faster

1. When to use escape_sql script command
 
.
input .@haha$; dispbottom .@haha$; dispbottom escape_sql(.@haha$); it doesn't has much differences, because it only affect 3 special characters
' <- single quotation mark
" <- double quotation mark
\ <- left slash
if I input -> haha"lala'hehe <-
it will return -> haha\"lala\'hehe <-
this is what we call, Escape a character
in hercules script, we also know we can use " symbol in any string input
mes "Susan says :\" Today I ate 3 eggs \"."; where in the game client, you can see the " symbol in the npc msg box
let's say I have a sql script like this
prontera,153,171,5 script Show Characters 1_F_MARIA,{ mes "input name, I'll show you all characters name it has on that player's account"; input .@name$; .@nb = query_sql("SELECT `char_id`, `name` FROM `char` WHERE `name` LIKE '"+ .@name$ +"'", .@cid, .@name$); if ( !.@nb ) { mes "no result"; close; } for ( .@i = 0; .@i < .@nb; ++.@i ) mes .@cid[.@i] +" "+ .@name$[.@i]; close; } this script has a possibility to be hacked
because to perform sql injection, I can enclose the string with quotation mark, then use another sql command to hack
BUT with an escape_sql command, if the user want to enclose the string with quotation mark to hack the script
the escape_sql command escaped the string, the quotation mark the user input will be escaped
thus the script will become impossible to hack
just now that script was for string input
prontera,153,171,5 script Show Characters 1_F_MARIA,{ mes "input account ID, I'll show you all characters name it has on that player's account"; input .@aid$; .@nb = query_sql("SELECT `char_id`, `name` FROM `char` WHERE `account_id` = "+ escape_sql(.@aid$), .@cid, .@name$); if ( !.@nb ) { mes "no result"; close; } for ( .@i = 0; .@i < .@nb; ++.@i ) mes .@cid[.@i] +" "+ .@name$[.@i]; close; } this is another stupid case.
1. the scripter use string input while the script just needed a number
2. even with escape_sql command over there, there is no quotation mark at all
yes this script also has a risk to be hack
because escape_sql only escape quotation mark.
that hacker don't even have to input quotation mark because it is a number
and an injection query can be sent without any quotation mark input
there are 2 ways to solve this
either use numeric variable for the input command
or enclose that ....
..... WHERE `account_id` = '"+ escape_sql(.@aid$) +"'", .... with single quotation mark, when the hacker input a quotation mark will be escaped by escape_sql command
Reference : https://www.w3schools.com/sql/sql_injection.asp
escape_sql command for another thing is
if the player register their names containing ' or ", these characters are escaped
only happens when the server have no restriction on the creation of players name
hercules\conf\char\char-server.conf
// Manage possible letters/symbol in the name of charater. Control character (0x00-0x1f) are never accepted. Possible values are: // NOTE: Applies to character, party and guild names. // 0: no restriction (default) // 1: only letters/symbols in 'name_letters' option. // 2: Letters/symbols in 'name_letters' option are forbidden. All others are possibles. name_option: 1  
and this was what happened to my SQL dota pvpladder script
Silo's Babies <-- this is a guild name
you can see the 5th string has a single quotation mark
with escape_sql command, that string will turn into
Silo\'s Babies <-- the quotation mark is escaped when send to sql query

2. How to build a case-sensitive table
this is the answer I found
http://dba.stackexchange.com/questions/15250/how-to-do-a-case-sensitive-search-in-where-clause
by default, the table creation use charset = latin1;
means it couldn't do a case-sensitive search
if you want to do a case-sensitive in a query, use BINARY
SELECT * FROM `char` WHERE `name` = BINARY('AnnieRuru'); however using BINARY might have performance hit if it is a big table
so its more recommend to convert your SQL table to collate with latin1_general_cs

let's say this is a sample table
CREATE TABLE `test` ( `id` INT(11) PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(23) )ENGINE = InnoDB; do an ALTER table syntax
ALTER TABLE `test` MODIFY COLUMN `name` VARCHAR(23) COLLATE latin1_general_cs; or just put it into the table creation
CREATE TABLE `test` ( `id` INT(11) PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(23) )ENGINE = InnoDB DEFAULT CHARSET = latin1 COLLATE latin1_general_cs;  

3. Choose a table type, MyISAM or InnoDB ?
https://stackoverflow.com/questions/20148/myisam-versus-innodb
Before MySQL 5.5,
MyISAM is mostly use for read-heavy + table locking storage engine = such as pvp ladder ( always select ... order by kill desc )
InnoDB is mostly use for write-heavy + row locking storage engine = such as quest script ( select ... from char_id ... only 1 row is retrieve )
After MySQL 5.6, (currently is 8.0)
just stick to InnoDB
there is only 1 reason MyISAM is better than InnoDB
- MyISAM use smaller disk usage than InnoDB
let's take a look at our MyISAM to InnoDB converter
https://github.com/HerculesWS/Hercules/blob/stable/sql-files/tools/convert_engine_innodb.sql
This converter is useful if you are using MySQL 5.6 or above
There are 4 tables that are commented out
the reason is simple, these 4 tables only read once and forgotten when server is live
since MyISAM is good at reading (SELECT) + smaller disk usage, its no use to convert these 4 tables into InnoDB
3a How to index a table properly
http://mysql.rjweb.org/doc.php/index_cookbook_mysql
http://www.dbta.com/Columns/DBA-Corner/Top-10-Steps-to-Building-Useful-Database-Indexes-100498.aspx
a simple thumb of rule, anything that is SELECT .... WHERE `field` = .....
that `field` has to be index
let's take a look at this PVP Ladder script that use Kill/Death ratio
CREATE TABLE `pvpladder` ( `char_id` INT(11), `name` VARCHAR(23), `kills` INT(11), `death` INT(11), PRIMARY KEY (`char_id`), KEY (`kills`, `death`) ) ENGINE = InnoDB; prontera,155,186,6 script PVP Ladder 1_F_MARIA,{ .@nb = query_sql( "SELECT `name`, `kills`/(`death`+1) FROM `pvpladder` WHERE `kills` > 0 ORDER BY `kills`/(`death`+1) DESC LIMIT 10", .@name$, .@ratio$ ); if ( !.@nb ) { mes "no entry"; close; } mes "Current Ranking :"; for ( .@i = 0; .@i < .@nb; ++.@i ) mes "No."+(.@i +1)+" ["+ .@name$[.@i] +"] "+ .@ratio$[.@i] +" kill"; close; OnPCKillEvent: if ( killedrid == getcharid(3) ) { // killing self should only increase death count. EG: Grand-cross query_sql "INSERT INTO `pvpladder` VALUES ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) )+"', 0,1 ) ON DUPLICATE KEY UPDATE `death` = `death` +1"; end; } query_sql "INSERT INTO `pvpladder` VALUES ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) )+"', 1,0 ) ON DUPLICATE KEY UPDATE `kills` = `kills` +1"; attachrid killedrid; query_sql "INSERT INTO `pvpladder` VALUES ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) )+"', 0,1 ) ON DUPLICATE KEY UPDATE `death` = `death` +1"; end; } This kind of query -> ORDER BY kills/death, needs to index them together like this
KEY (`kills`, `death`) 3b. Why you shouldn't use `char_reg_num_db` table
blame Euphy for spreading this technique
There are 2 reasons why you shouldn't even touch all these variable tables
Reason no.1 This table is sorely meant for server usage
Once these data is loaded, it is process internally, and only save character data according to this configuration
Reason no.2 The `value` field is not index !
This line has ORDER BY `value`, try recheck our main.sql file
CREATE TABLE IF NOT EXISTS `acc_reg_num_db` ( `account_id` INT(11) UNSIGNED NOT NULL DEFAULT '0', `key` VARCHAR(32) BINARY NOT NULL DEFAULT '', `index` INT(11) UNSIGNED NOT NULL DEFAULT '0', `value` INT(11) NOT NULL DEFAULT '0', PRIMARY KEY (`account_id`,`key`,`index`), KEY `account_id` (`account_id`) ) ENGINE=MyISAM; SQL will search through every single line in the `value` field if that column isn't index
Of course you can ... do ALTER table to add KEY to the `value` field
but this table has already optimized in that way for server usage
the more field you index into the table, the more disk usage space it use
Conclusion : If you want to make a custom script, then make a custom table. Leave these table alone !

3. Choose a table type, MyISAM or InnoDB ?
https://stackoverflow.com/questions/20148/myisam-versus-innodb
Before MySQL 5.5,
MyISAM is mostly use for read-heavy + table locking storage engine = such as pvp ladder ( always select ... order by kill desc )
InnoDB is mostly use for write-heavy + row locking storage engine = such as quest script ( select ... from char_id ... only 1 row is retrieve )
After MySQL 5.6, (currently is 8.0)
just stick to InnoDB
there is only 1 reason MyISAM is better than InnoDB
- MyISAM use smaller disk usage than InnoDB
let's take a look at our MyISAM to InnoDB converter
https://github.com/HerculesWS/Hercules/blob/stable/sql-files/tools/convert_engine_innodb.sql
This converter is useful if you are using MySQL 5.6 or above
There are 4 tables that are commented out
the reason is simple, these 4 tables only read once and forgotten when server is live
since MyISAM is good at reading (SELECT) + smaller disk usage, its no use to convert these 4 tables into InnoDB
3a How to index a table properly
http://mysql.rjweb.org/doc.php/index_cookbook_mysql
http://www.dbta.com/Columns/DBA-Corner/Top-10-Steps-to-Building-Useful-Database-Indexes-100498.aspx
a simple thumb of rule, anything that is SELECT .... WHERE `field` = .....
that `field` has to be index
let's take a look at this PVP Ladder script that use Kill/Death ratio
CREATE TABLE `pvpladder` ( `char_id` INT(11), `name` VARCHAR(23), `kills` INT(11), `death` INT(11), PRIMARY KEY (`char_id`), KEY (`kills`, `death`) ) ENGINE = InnoDB; prontera,155,186,6 script PVP Ladder 1_F_MARIA,{ .@nb = query_sql( "SELECT `name`, `kills`/(`death`+1) FROM `pvpladder` WHERE `kills` > 0 ORDER BY `kills`/(`death`+1) DESC LIMIT 10", .@name$, .@ratio$ ); if ( !.@nb ) { mes "no entry"; close; } mes "Current Ranking :"; for ( .@i = 0; .@i < .@nb; ++.@i ) mes "No."+(.@i +1)+" ["+ .@name$[.@i] +"] "+ .@ratio$[.@i] +" kill"; close; OnPCKillEvent: if ( killedrid == getcharid(3) ) { // killing self should only increase death count. EG: Grand-cross query_sql "INSERT INTO `pvpladder` VALUES ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) )+"', 0,1 ) ON DUPLICATE KEY UPDATE `death` = `death` +1"; end; } query_sql "INSERT INTO `pvpladder` VALUES ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) )+"', 1,0 ) ON DUPLICATE KEY UPDATE `kills` = `kills` +1"; attachrid killedrid; query_sql "INSERT INTO `pvpladder` VALUES ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) )+"', 0,1 ) ON DUPLICATE KEY UPDATE `death` = `death` +1"; end; } This kind of query -> ORDER BY kills/death, needs to index them together like this
KEY (`kills`, `death`) 3b. Why you shouldn't use `char_reg_num_db` table
blame Euphy for spreading this technique
There are 2 reasons why you shouldn't even touch all these variable tables
Reason no.1 This table is sorely meant for server usage
Once these data is loaded, it is process internally, and only save character data according to this configuration
Reason no.2 The `value` field is not index !
This line has ORDER BY `value`, try recheck our main.sql file
CREATE TABLE IF NOT EXISTS `acc_reg_num_db` ( `account_id` INT(11) UNSIGNED NOT NULL DEFAULT '0', `key` VARCHAR(32) BINARY NOT NULL DEFAULT '', `index` INT(11) UNSIGNED NOT NULL DEFAULT '0', `value` INT(11) NOT NULL DEFAULT '0', PRIMARY KEY (`account_id`,`key`,`index`), KEY `account_id` (`account_id`) ) ENGINE=MyISAM; SQL will search through every single line in the `value` field if that column isn't index
Of course you can ... do ALTER table to add KEY to the `value` field
but this table has already optimized in that way for server usage
the more field you index into the table, the more disk usage space it use
Conclusion : If you want to make a custom script, then make a custom table. Leave these table alone !

As usual, I only write advance guides
This guide is a compilation of SQL commands that I have used, or Questions answered on the forum
every single subject here are related to Hercules/Ragnarok Online in some ways, so you won't feel bored reading them XD
Table of Content
1. When to use *escape_sql script command
2. How to build a case-sensitive table
3. Choose a table type, MyISAM or InnoDB ?
3a. How to index a table properly
3b. Why you shouldn't use `char_reg_num_db` table
4. AUTO_INCREMENT
5. How to do IF-ELSE in SQL query ?
5a. How to update multiple rows on different conditions in a single query
6. How to show the current rank of the player
7. INSERT INTO ... SELECT ...
8. Table JOIN vs AS
9. What is the maximum string limit for *query_sql
9a. UNION
This topic is now open to Suggestions, Ideas, Improvements, and Questions ~
I'm sure many of you have some questions since the creation of this topic
You may also post up your tricks if you want to share with us