Excessive response time between MySQL and PHP

6

I am making an application which validates certain user data by checking if the provided data exists in the database, the responses of the database can take at least 3 seconds, but making the queries directly in the database , the answers are immediate, no more than 50ms. I have a connection class in PHP, which connects me to the database with PDO and static methods to execute queries or select data, I will leave some methods of each class.

//Metodos de la clase Connection

//Método para conectarme a la base de datos
public static function connect() {
    try {
    self::$connection = new PDO(self::$server, self::$user, self::$password);
        self::$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    catch(PDOException $e) {
        echo $e->getMessage();
    }
}

//Método para desconectarme
public static function disconnect() {
    try {
        self::$connection = NULL;
    }
    catch(PDOException $e) {
        echo $e->getMessage();
    }
}

//Método para seleccionar con una consulta preparada
public static function select(string $query, array $array = NULL) : PDOStatement {
    try {
        self::connect();
        $sth = self::$connection->prepare($query, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
        $sth->execute($array);
        return $sth;
    }
    catch(PDOException $e){
        echo $e->getMessage();
        return $sth = new PDOStatement();
    }
    finally {
        self::disconnect();
    }
}

I have another class which is for the user, where you have static methods to do validations, which have been problems for me because they take too long to respond, such as a simple query that returns a 1 if you have a coincidence. The following code represents a static method which returns true or false depending on the response of the database. As mentioned earlier, queries are prepared to avoid SQL Injection attacks, maybe that can influence the slow response.

public static function usernameExist(string $username) : bool {
    $query = "SELECT EXISTS(SELECT 1 FROM users WHERE username_user = :username LIMIT 1);";
    $sth = Connection::select($query, ["username" => $username]);
    $r = $sth->fetchObject();
    return $sth->rowCount() === 1 ? true : false;
}

The previous codes work, the problem is the time, since they do not do too many processes to take so long.

All PHP files start with the following code:

declare(strict_types=1);
error_reporting(E_ALL ^ E_NOTICE);
header('Content-type:application/json');

I use the version of XAMPP 7.2.10 / PHP 7.2.10 MySQL version 5.7.23 The application is in localhost I do not use any code debugger

How can I solve it? What can I use?

Greetings.

UPDATE I came to a conclusion, all the code is fine, but I have been doing tests with another database and other functions that PHP offers and when executing line by line of code I have realized that what causes the problem is the following line of a new example:

$conexion = new PDO("mysql:host=localhost;dbname=prueba;charset=utf8", "root", "");
    
asked by Aleex 13.10.2018 в 01:39
source

1 answer

1

The problem may be that you are establishing the connection every time you call Connection::select .

Usually the connection to the database is made only once and remains open throughout the life of the program.

You should try to make the connection in the object constructor and the disconnection in the destructor. Another alternative would be to have the connection as a global variable.

    
answered by 19.10.2018 в 02:00