Viiarge Viiarge - 4 months ago 17
MySQL Question

Why is this php script running so slow (Simple select)

This script is running really slowly when I try it in google chrome with the script URL , and I would like to know why:

<?php

include 'config.php';

$conn = mysqli_connect($servername, $username, $password, $dbname);

$playerEmail = $_REQUEST["playerEmail"];

$sql = "SELECT *
FROM players
WHERE EMAIL = '$playerEmail'";

$res = mysqli_query($conn,$sql);

$result = array();

while($row = mysqli_fetch_array($res)){
array_push($result,
array('EMAIL'=>$row[0],
'DATEOFSIGNUP'=>$row[2],
'USERNAME'=>$row[3],
'GENDER'=>$row[4],
'JOB'=>$row[5],
'LVL'=>$row[6],
'HP_NOW'=>$row[7],
'HP_MAX'=>$row[8],
'MANA_NOW'=>$row[9],
'MANA_MAX'=>$row[10],
'STR'=>$row[11],
'SPD'=>$row[12],
'INTEL'=>$row[13],
'XP_NOW'=>$row[14],
'XP_NEEDED'=>$row[15],
'GOLDS'=>$row[16],
'NUMBERSOFITEMS'=>$row[17],
'LOCATION_X'=>$row[18],
'LOCATION_Y'=>$row[19]
));
}

echo json_encode(array("result"=>$result));

mysqli_close($conn);

?>


All my others scripts were made using $_REQUEST, so I dont believe it's the cause. Anything you can think off, feel free to share here.

Thanks alot folks !

Answer

The answer to create an index on your EMAIL column is correct, but for future reference here's a quicker, safer way to code this with PDO:

<?php

include 'config.php';

try {
    $pdo = new PDO("mysql:host=$servername;dbname=$dbname",
        $username, $password, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
} catch (PDOException $e) {
    error_log("PDO connection failed in " . __FILE__ . 
        ", Error: " . $e->getMessage());
    die("Database error, please contact administrator");
}

$playerEmail = $_REQUEST["playerEmail"];

$sql = "SELECT EMAIL, DATEOFSIGNUP, USERNAME, GENDER,
          JOB, LVL, HP_NOW, HP_MAX, MANA_NOW, MANA_MAX,
          STR, SPD, INTEL, XP_NOW, XP_NEEDED, GOLDS,
          NUMBERSOFITEMS, LOCATION_X, LOCATION_Y
    FROM players
    WHERE EMAIL = ?";

$stmt = $pdo->prepare($sql);
$stmt->execute([$playerEmail]);

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo json_encode(array("result"=>$result));