MySQL how get all variables from table at once

MySQL how get all variables from table at once - 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 how get all variables from table at once (/showthread.php?tid=635711)

MySQL how get all variables from table at once - bigboy81 - 12.06.2017

I made mysql connection in my gamemode.
But when i login, server needs 2678 miliseconds for loading all variables at once.

That is too much for me, also i tested with Y_ini system which read fast those variables, and with him is not so.

I'm interested in your opinion
I'm using MYSQL R39 version

PHP код:



CONNECT

{

    new query[100], playername[MAX_PLAYER_NAME];

    GetPlayerName(playerid, playername, sizeof(playername));

    mysql_format(mysql, query, sizeof(query), "SELECT * FROM `Players` WHERE `Name` = '%e' LIMIT 1", playername);

    mysql_tquery(mysql, query, "OnAccountLoad", "i", playerid);

}



LOAD

{

    forward OnAccountLoad(playerid);

    public OnAccountLoad(playerid)

    {

        PlayerInfo[playerid][pLevel] = cache_get_field_content_int(0, "Level");

        PlayerInfo[playerid][pPosX] = cache_get_field_content_float(0, "PosX");

        PlayerInfo[playerid][pPosY] = cache_get_field_content_float(0, "PosY");

        PlayerInfo[playerid][pPosZ] = cache_get_field_content_float(0, "PosZ");

        PlayerInfo[playerid][pPosA] = cache_get_field_content_float(0, "PosA");

        PlayerInfo[playerid][pHealth] = cache_get_field_content_float(0, "Health");

        PlayerInfo[playerid][pArmor] = cache_get_field_content_float(0, "Armor");

        .

        .

        .

        .

        .

        200 Variables like those is here

        return 1;

    }

}

Sorry for my bad writting in English i hope you understand me.

Re: MySQL how get all variables from table at once - MEW273 - 12.06.2017

Do you have MySQL logging enabled? If so can you post the log when this query is executed here or on Pastebin.

Re: MySQL how get all variables from table at once - bigboy81 - 12.06.2017

Quote:

Originally Posted by MEW273

Do you have MySQL logging enabled? If so can you post the log when this query is executed here or on Pastebin.

Yes of course i use mysql logging.
Here is some part from it..

PHP код:



[12:06:53 06/12/17] [DEBUG] CMySQLResult::~CMySQLResult() - deconstructor called

[12:07:57 06/12/17] [DEBUG] mysql_format - connection: 1, len: 4028, format: "UPDATE `Players` SET `Level` = '%d', `RPPoens` = '%d', `GameMaster` = '%d', `AdminLevel` = '%d', `RegularRank` = '%d', `DonateRank..."

[12:07:57 06/12/17] [DEBUG] mysql_tquery - connection: 1, query: "UPDATE `Players` SET `Level` = '1', `RPPoens` = '0', `GameMaster` ", callback: "(null)", format: "(null)"

[12:07:57 06/12/17] [DEBUG] mysql_format - connection: 1, len: 4028, format: "UPDATE `Players` SET `Sex` = '%d', `Age` = '%d', `GPS` = '%d', `StealedPhones` = '%d', `StealedWatchs` = '%d', `Origin` = '%d'..."

[12:07:57 06/12/17] [DEBUG] CMySQLQuery::Execute[] - starting query execution

[12:07:57 06/12/17] [DEBUG] mysql_tquery - connection: 1, query: "UPDATE `Players` SET `Sex` = '1', `Age` = '20', `GPS` = '0', `Ukra", callback: "(null)", format: "(null)"

[12:07:57 06/12/17] [DEBUG] mysql_format - connection: 1, len: 4028, format: "UPDATE `users` SET `Bank` = '%d', `Card` = '%d', `Crimes` = '%d', `CrimeReason` = '%e', `Wanted` = '%d', `WantedLevel` = '%d'..."

[12:07:57 06/12/17] [DEBUG] mysql_tquery - connection: 1, query: "UPDATE `Players` SET `Bank` = '0', `Kartica` = '0', `Crimes` = '0'", callback: "(null)", format: "(null)"

[12:07:57 06/12/17] [DEBUG] mysql_format - connection: 1, len: 4028, format: "UPDATE `users` SET `Fishes` = '%d', `BiggestFish` = '%d', `Job` = '%d', `Paycheck` = '%d', `HeadValue` = '%d', `Jailed` = '%d', ..."

[12:07:57 06/12/17] [DEBUG] mysql_tquery - connection: 1, query: "UPDATE `Players` SET `Fishes` = '0', `BiggestFish` = '0', `Job` = ", callback: "(null)", format: "(null)"

[12:07:57 06/12/17] [DEBUG] mysql_format - connection: 1, len: 4028, format: "UPDATE `Players` SET `FMemberOf` = '%d', `RankNo` = '%d', `Char` = '%d', `ContractTime` = '%d', `DetSkill` = '%d', `SexSkill` = '%..."

[12:07:57 06/12/17] [DEBUG] mysql_tquery - connection: 1, query: "UPDATE `Players` SET `FMemberOf` = '255', `RankNo` = '0', `Char` =", callback: "(null)", format: "(null)"

[12:07:57 06/12/17] [DEBUG] CMySQLQuery::Execute[] - query was successfully executed within 38.799 milliseconds

[12:07:57 06/12/17] [DEBUG] CMySQLQuery::Execute[] - no callback specified, skipping result saving

Re: MySQL how get all variables from table at once - Vince - 12.06.2017

Quote:

Originally Posted by bigboy81

server needs 2678 seconds

Don't you mean milliseconds?

Furthermore, if you have over 200 columns in a single table then your database design is probably bad. Yes, this may sound harsh but I'm saying it like it is. Key elements in determining whether your design is bad:

You add a number to a column name (e.g. Weapon1, Weapon2, Weapon3, ...).
You have suspiciously similar column names (e.g. DriverLicense, FlyingLicense, FishingLicense, ...).
You have a lot of columns with values that are mostly zero or null.

In almost all instances it is better to extract those columns to (a) different table(s). Anything that does not apply to each and every player should not be in the main player table.

Lastly, put an index on columns that are frequently used in where-clauses or join-clauses. If the column holds unique values (like a name) then also put a unique constraint to speed up processing even more.

Re: MySQL how get all variables from table at once - bigboy81 - 12.06.2017

Quote:

Originally Posted by MEW273

Do you have MySQL logging enabled? If so can you post the log when this query is executed here or on Pastebin.

Quote:

Originally Posted by Vince

You add a number to a column name (e.g. Weapon1, Weapon2, Weapon3, ...).
You have suspiciously similar column names (e.g. DriverLicense, FlyingLicense, FishingLicense, ...).
You have a lot of columns with values that are mostly zero or null.

Yes i mean in miliseconds..
as regards mysql saving system i don't have problems.
But only when i log in i must wait some short time.

Re: MySQL how get all variables from table at once - Konstantinos - 12.06.2017

Having DEBUG enabled will load everything much slower. Keep the default one (errors, warnings and probably info).

Re: MySQL how get all variables from table at once - bigboy81 - 12.06.2017

Quote:

Originally Posted by Konstantinos

Having DEBUG enabled will load everything much slower. Keep the default one (errors, warnings and probably info).

Thank you, now works like normal..