how to avoid connecting to mysql database with every request in php?

Question

i have a simple php page that query the database for a random row (with some exceptions), every time it gets a post request.

i have noticed that the post request take some time to get the response.
because with every post request it reconnect to mysql server, so i thought maybe i could store the pdo in $_SESSION and use it to save some time and not have to authenticate with every request

i don't know if there is any way to fix this :)

<?php
// check for POST Request Only
if($_SERVER['REQUEST_METHOD'] !== 'POST') {
    header("HTTP/1.0 405 Method Not Allowed");
    exit();
}
$host = 'localhost';
$db   = 'Quiz';
$user = 'admin';
$pass = 'password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
try {
    $pdo = new PDO($dsn, $user, $pass, $options);

    if (!empty($_POST['old'])) {
        $in  = str_repeat('?,', count($_POST['old']) - 1) . '?';
        $sql = "SELECT * FROM QT WHERE id NOT IN ($in) ORDER BY RAND() LIMIT 1";
        $stm = $pdo->prepare($sql);
        $stm->execute($_POST['old']);
        $data = $stm->fetchAll();
        echo json_encode($data);
        exit();

    } else {
        $stm = $pdo->prepare('SELECT * FROM QT ORDER BY RAND() LIMIT 1');
        $stm->execute();
        $data = $stm->fetchAll();
        echo json_encode($data);
        exit();

    }
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

?>

I don't have enough rep to comment, but this is related to: [Connection pooling in PHP](https://stackoverflow.com/questions/39753/connection-pooling-in-php) — Randy Wallace, Apr 24 '20 at 04:47

score 3 · Answer 1 · edited Jun 20 '20 at 09:12

Сonnecting to mysql database with every request is how

PHP intended to work

Nothing wrong here, nothing to optimize here.

Go connect on every request and next time try to optimize a real issue.

so i thought

Don't think. Measure.

From the experience I can tell you that its the query that takes the time, not the connection. But technically that would be a guess all the same. So you have to measure first, then get the real issue, then google for it, solutions are plenty.

But measure first.

how to avoid connecting to mysql database with every request in php?

1 Answers1

PHP intended to work