MySQL how get all variables from table at once
#1

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(playeridplayernamesizeof(playername));
    
mysql_format(mysqlquerysizeof(query), "SELECT * FROM `Players` WHERE `Name` = '%e' LIMIT 1"playername);
    
mysql_tquery(mysqlquery"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.
Reply
#2

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

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] [DEBUGCMySQLResult::~CMySQLResult() - deconstructor called
[12:07:57 06/12/17] [DEBUGmysql_format connection1len4028format"UPDATE `Players` SET `Level` = '%d', `RPPoens` = '%d', `GameMaster` = '%d', `AdminLevel` = '%d', `RegularRank` = '%d', `DonateRank..."
[12:07:57 06/12/17] [DEBUGmysql_tquery connection1query"UPDATE `Players` SET `Level` = '1', `RPPoens` = '0', `GameMaster` "callback"(null)"format"(null)"
[12:07:57 06/12/17] [DEBUGmysql_format connection1len4028format"UPDATE `Players` SET `Sex` = '%d', `Age` = '%d', `GPS` = '%d', `StealedPhones` = '%d', `StealedWatchs` = '%d', `Origin` = '%d'..."
[12:07:57 06/12/17] [DEBUGCMySQLQuery::Execute[] - starting query execution
[12:07:57 06/12/17] [DEBUGmysql_tquery connection1query"UPDATE `Players` SET `Sex` = '1', `Age` = '20', `GPS` = '0', `Ukra"callback"(null)"format"(null)"
[12:07:57 06/12/17] [DEBUGmysql_format connection1len4028format"UPDATE `users` SET `Bank` = '%d', `Card` = '%d', `Crimes` = '%d', `CrimeReason` = '%e', `Wanted` = '%d', `WantedLevel` = '%d'..."
[12:07:57 06/12/17] [DEBUGmysql_tquery connection1query"UPDATE `Players` SET `Bank` = '0', `Kartica` = '0', `Crimes` = '0'"callback"(null)"format"(null)"
[12:07:57 06/12/17] [DEBUGmysql_format connection1len4028format"UPDATE `users` SET `Fishes` = '%d', `BiggestFish` = '%d', `Job` = '%d', `Paycheck` = '%d', `HeadValue` = '%d', `Jailed` = '%d', ..."
[12:07:57 06/12/17] [DEBUGmysql_tquery connection1query"UPDATE `Players` SET `Fishes` = '0', `BiggestFish` = '0', `Job` = "callback"(null)"format"(null)"
[12:07:57 06/12/17] [DEBUGmysql_format connection1len4028format"UPDATE `Players` SET `FMemberOf` = '%d', `RankNo` = '%d', `Char` = '%d', `ContractTime` = '%d', `DetSkill` = '%d', `SexSkill` = '%..."
[12:07:57 06/12/17] [DEBUGmysql_tquery connection1query"UPDATE `Players` SET `FMemberOf` = '255', `RankNo` = '0', `Char` ="callback"(null)"format"(null)"
[12:07:57 06/12/17] [DEBUGCMySQLQuery::Execute[] - query was successfully executed within 38.799 milliseconds
[12:07:57 06/12/17] [DEBUGCMySQLQuery::Execute[] - no callback specifiedskipping result saving 
Reply
#4

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.
Reply
#5

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
Посмотреть сообщение
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.
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.
Reply
#6

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

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..
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)