Excessive response time between MySQL and PHP

Question:

I am making an application which validates certain user data by checking if the data provided exists in the database, the responses from the database may take at least 3 seconds, but doing the queries directly in the database, the responses they 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 from 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 it has static methods to make validations, which have been problems for me since they take too long to respond, such as a simple query that returns a 1 if it has a match. The following code represents a static method which returns true or false depending on the response from the database. As mentioned above, the queries are prepared to avoid SQL Injection attacks, maybe that can influence the slow response time.

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 above codes work, the problem is the time, since they do not do too many processes to take that 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 XAMPP version 7.2.10 / PHP 7.2.10 MySQL version 5.7.23 The application is on localhost I do not use any code debuggers

How can I solve that? 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 from a new example:

$conexion = new PDO("mysql:host=localhost;dbname=prueba;charset=utf8", "root", "");

Answer:

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

Normally, the connection to the database is made only once and is kept open throughout the life of the program.

You should try connecting in the object's constructor and disconnecting in the destructor. Another alternative would be to have the connection as a global variable.

Scroll to Top