MySQL optimization - Printable Version

MySQL optimization - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+--- Thread: MySQL optimization (/showthread.php?tid=672526)

MySQL optimization - Halinix - 16.02.2020

Good evening everyone, I need your help. Is there a guide I have to follow to create a MySQL database optimized to the maximum, avoiding unnecessary lines etc? Please help me. Thanks in advance.

Re: MySQL optimization - Symon - 16.02.2020

Depends on which is your use. Registration? If so, just save username, password and salt (if you have it).

Re: MySQL optimization - Halinix - 16.02.2020

Quote:

Originally Posted by Symon

Depends on which is your use. Registration? If so, just save username, password and salt (if you have it).

More than anything else, I am looking for suggestions or a guide on how to build a database correctly and optimally optimized. The database must contain the server data (for example: vehicles, houses, users etc). I have already made a MySQL database, but since I am a self-taught, I don't know the way or better the correct method to create it, so I am looking for a guide that will explain and teach me how to do it properly, correctly following the rules and very specific rules. And I am looking for this help on this forum because there are people like you who I think are really good at these things and consequently I would like to listen to your advice.
Thanks in advance.

Re: MySQL optimization - Symon - 17.02.2020

Here are some advices for you (and for who is reading).

Imagine you want to create a "players" table which will handle your players stats. Our enum will be like this:

pawn Code:

enum E_PLAYERS
{
    ID,
    Name[MAX_PLAYER_NAME],
    Password[65],
    Salt[17],
    Kills
}
new Player[MAX_PLAYERS][E_PLAYERS];

ID: Set it as auto increment and PRIMARY KEY as it will handle the player's account database ID (NOT the player ID) - Create it as int.
Username: Set as VARCHAR (24) - since 24 is the MAX_PLAYER_NAME length.

Password: This depends on how you save player's password.
I suggest you to NOT save them in plain text (just like many people do, even in 2020) but encoding them with SHA-256. If you want an extra security, salt them and compare both password and salt on login:

pawn Code:

for(new i = 0; i < 16; i++) Player[playerid][Salt][i] = random(94) + 33;
SHA256_PassHash(inputtext, Player[playerid][Salt], Player[playerid][Password], 65);

This will generate a random salt and will encode the player's password. In this case for both Password and Salt you can use VARCHAR (65 and 17).

After salting and enconding, this is how the registation query would be:

pawn Code:

new regQuery[200];

mysql_format(g_SQL, regQuery, sizeof regQuery, "INSERT INTO `your_accounts_table` (`Username`, `Password`, `Salt`) VALUES ('%e', '%s', '%e')", ReturnPlayerName(playerid), Player[playerid][Password], Player[playerid][Salt]);
mysql_tquery(g_SQL, regQuery, "OnPlayerRegister", "d", playerid);

Look how i used "OnPlayerRegister" - it's a threaded query.

pawn Code:

forward OnPlayerRegister(playerid)
public OnPlayerRegister(playerid)
{
    Player[playerid][ID] = cache_insert_id();
    return 1;
}

Remember to set default values to 0, such as "Kills" directly when created your accounts table, this would avoid you to save unnecessary values when calling OnPlayerRegister.

In short word: Just save Username, Password and Salt - the rest should be set to 0 as default values, MySQL will do the rest.

Look how i used cache_insert_int in the [ID], which will handle the player's account database ID.

Then on login, campare the Salt and Password, if both are correct, let the player login:

pawn Code:

new hashed_pass[65];
SHA256_PassHash(inputtext, Player[playerid][Salt], hashed_pass, 65);

if(strcmp(hashed_pass, Player[playerid][Password]) == 0)
{
    //Password is correct, load your player's stats here

    LoadPlayerStats(playerid);
}

To load stats it's more than easy, add something like LoadPlayerStats(playerid) inside the salt/password check, then:

pawn Code:

LoadPlayerStats(playerid)
{
    cache_get_value_int(0, "ID", Player[playerid][ID]);
    cache_get_value_int(0, "Kills", Player[playerid][Kills]);

    //give money, set skin etc.
}

DON'T load the salt as is not needed.

Now, to save player's stats you have 2 methods:

1) You can use an UPDATE query everytime a stat changes (like money, kills or deaths) - Example on OnPlayerDeath:

pawn Code:

if(killerid != INVALID_PLAYER_ID)
{
    Player[killerid][Kills] ++;

    new upKillsQuery[70];

    mysql_format(g_SQL, upKillsQuery, sizeof(upKillsQuery), "UPDATE `players` SET `Kills` = %d WHERE `ID` = %d", Player[playerid][Kills], Player[playerid][ID]);
    mysql_query(g_SQL, upKillsQuery);
}

What we did here? We saved the player's kills associated with their account database ID, you can also replace WHERE `ID` with WHERE `Username` and use the username instead.

2) You can create a function, such as:

pawn Code:

stock SaveAccountStats(playerid)
{
    //Insert your UPDATE query here for everything
    return 1;
}

Which you can update ALL player's stats such as money, kills, deaths etc. in one shot, to use on OnPlayerDisconnect (so it will save stats when the player leaves), your choice.

Re: MySQL optimization - Calisthenics - 17.02.2020

Database normalization and correct use of indexes.

https://en.wikipedia.org/wiki/Databa...n#Normal_forms
https://www.guru99.com/database-normalization.html

http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Re: MySQL optimization - Halinix - 18.02.2020

thx guys

Re: MySQL optimization - CXdur - 19.02.2020

I see that someone already posted regarding normalization, so I'll do query optimization:

Analyzing queries (see how they run, if as intended)
https://dev.mysql.com/doc/refman/5.7...g-explain.html
Things to consider for optimizing queries:
https://dev.mysql.com/doc/refman/5.7...imization.html