Fastest Way Of Getting Informations

Hello, so I basically, while developing my plugins, I noticed that the server needs to hit a lot the database (to get the player's guild, factions, language , kills, deaths, XP, level, group, etc etc etc. But it makes the operations long which creates small little lags in the server and I want it as smooth as possible. So I was thinking about instead of hitting the MySQL database every times I do something, I create a array variable onEnable and onJoin, I add the player and select all the informations I need so I would get an array of players and inside example CupidonSauce173, there's another array with all the informations needed (faction,guild,level,rank, blablabla) and when there is something I need to update, I just modify the array and example, every 30 mins or when the server reboot, it updates the database with all the new values. Would that be a good alternative ? Or is there any better way of using the database but still get good performances?
I know it might use a lot of ram but I have 16gb of them, it's not a 1500 players server so the variable won't really be a problem in terms of ram usage.
Thanks for the help

 

Your method could work but if you have a lot of players will be very memory heavy. Use Asynql by SOFe for better database interactions

 

I might look at it but yeah..for example /friends add <player>, it looks if the target is registered, then look if they are friends (so get the friends list), then if not, it adds the sender to the requests list and send a instant notification + a normal notification to the target, so it's a lot of hits on the database

Would that affect the performances or risk of having variable failures or something?

 

Well, for people who are interested.
My solution on that was to stop using MySQL as *main* database and going for json files, what happens is that the servers in the network will save/get the informations in database.json (so this is like a temporary database) and an external script will convert the informations from the json file to the SQL database every 1 hour so pmmp doesn't have to do it. Now, for example, onProJoin doesn't take 0.42 s to execute but 0.2 s and for all the other operations, it won't need to hit the SQL database. I am not sure if this is the best idea or if it's okay but in my situation, it works well.

 

Just store temp data in vars. When player disconnects, save all information whose belongs to player and when connects, load data from the database.

 

Yes, but no. Good idea for when you only have one server, but in my situation, there's more than one server so as I said, I just use a JSON and all instances interact with it.

 

Storage isn't fast as well. Updating (saving) files on every join/quit/small update also causes small bugs. It depends on disk, player cont, etc. I'm using this way with more servers. I'm using async task just when player logs/disconnects... When player submits transfer request, I cancell it and save the data to mysql server, when the mysql is done, I transfer the player...

 

Does other players can see stats of another player in real time? Because that was one of my major issues, there is stuff like friends or player stats (kill, coins, deaths, that other players can see or interact with those stats from other servers.
And you can use MySQL on AsyncTask? I didn't know, do you have an example of that task? If you don't mind

 

idk how it is on your server, but I have it that player got transferred after every minigame, so players on the lobby have almost the newest information.

 

Spoiler: AsyncQuery.php

PHP:

<?php

declare(strict_types=1);

namespace vixikhd\mysqlapi\query;

use pocketmine\scheduler\AsyncTask;

/**
 * Class AsyncQuery
 * @package vixikhd\mysqlapi\query
 */
abstract class AsyncQuery extends AsyncTask {

    private const LOGIN = "Login";
    private const PASSWORD = "Password";
    private const DATABASE = "MySecretDatabase";
    private const HOST = "x.x.x.x";
    //private const HOST = "127.0.0.1";

    /**
     * @return \mysqli
     */
    protected function getMysqli(): \mysqli {
        return new \mysqli(self::HOST, self::LOGIN, self::PASSWORD, self::DATABASE);
    }

    /**
     * @param array $result
     * @return array
     */
    protected function rebuildResult(array $result): array {
        $return = [];
        foreach ($result as $index => $array) {
            foreach ($array as $value) {
                $return[$index] = $value;
            }
        }
        return $return;
    }
}

Spoiler: ConnectQuery.php

PHP:

<?php

declare(strict_types=1);

namespace vixikhd\mysqlapi\query;

use vixikhd\mysqlapi\MySQLApi;
use pocketmine\Server;

/**
 * Class ConnectQuery
 * @package vixikhd\mysqlapi\query
 */
class ConnectQuery extends AsyncQuery {

    public function onRun() {
        try {
            /** @var \mysqli $mysqli */
            $mysqli = $this->getMysqli();
            if($mysqli->connect_error) {
                throw new \Exception($mysqli->connect_error);
            }
            $this->setResult(["connected" => true, "message" => ""]);
        }
        catch (\Exception $exception) {
            $this->setResult(["connected" => false, "message" => $exception->getMessage()]);
        }
    }

    /**
     * @param Server $server
     */
    public function onCompletion(Server $server) {
        $plugin = MySQLApi::getInstance();
        $result = $this->getResult();
        if($result["connected"]) {
            $plugin->getLogger()->info("§aSuccessfully connected to MySQL database!§f");
            return;
        }
        $plugin->getLogger()->info("§cCould not connect to MySQL database ({$result["message"]})");
    }
}