SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Scripting Help

Reply
 
Thread Tools Display Modes
Old 16/02/2020, 08:32 PM   #1
Halinix
Little Clucker
 
Join Date: Feb 2020
Posts: 9
Reputation: 0
Default MySQL optimization

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.
Halinix is offline   Reply With Quote
Old 16/02/2020, 08:35 PM   #2
Symon
Gangsta
 
Symon's Avatar
 
Join Date: Apr 2019
Location: Italy
Posts: 989
Reputation: 107
Default Re: MySQL optimization

Depends on which is your use. Registration? If so, just save username, password and salt (if you have it).
__________________
Epic Missions - An upcoming mini missions server.

Fully MySQL featured, no server restart during a mission change, many different mission types available (TDM, DM, BOMBING, LMS, LTS etc.), perks, rewards, level system and much more. Join us and wait for the launch!


Website | Forum | Development logs | Join our Discord
Symon is offline   Reply With Quote
Old 16/02/2020, 09:31 PM   #3
Halinix
Little Clucker
 
Join Date: Feb 2020
Posts: 9
Reputation: 0
Default Re: MySQL optimization

Quote:
Originally Posted by Symon View Post
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.
Halinix is offline   Reply With Quote
Old 17/02/2020, 08:52 AM   #4
Symon
Gangsta
 
Symon's Avatar
 
Join Date: Apr 2019
Location: Italy
Posts: 989
Reputation: 107
Default Re: MySQL optimization

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.
__________________
Epic Missions - An upcoming mini missions server.

Fully MySQL featured, no server restart during a mission change, many different mission types available (TDM, DM, BOMBING, LMS, LTS etc.), perks, rewards, level system and much more. Join us and wait for the launch!


Website | Forum | Development logs | Join our Discord
Symon is offline   Reply With Quote
Old 17/02/2020, 09:27 AM   #5
Calisthenics
High-roller
 
Join Date: May 2018
Posts: 1,228
Reputation: 205
Default Re: MySQL optimization

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
__________________
Calisthenics is offline   Reply With Quote
Old 18/02/2020, 09:55 PM   #6
Halinix
Little Clucker
 
Join Date: Feb 2020
Posts: 9
Reputation: 0
Default Re: MySQL optimization

thx guys
Halinix is offline   Reply With Quote
Old 19/02/2020, 11:57 AM   #7
CXdur
Big Clucker
 
CXdur's Avatar
 
Join Date: Jun 2014
Location: Norway
Posts: 57
Reputation: 6
Default Re: MySQL optimization

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
CXdur is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
MySQL Optimization for Server SkyFlare Scripting Help 4 31/01/2019 11:04 AM
MySQL/Memory optimization Germanator Help Archive 3 18/06/2011 03:33 AM
MYSQL Optimization ombre Help Archive 0 20/02/2011 04:33 AM
MySql (optimization) DRIFT_HUNTER Help Archive 5 18/01/2011 09:05 AM
A Little mysql optimization wups Help Archive 9 15/10/2010 04:43 PM


All times are GMT. The time now is 12:18 PM.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.