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

Question

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 !

Which section is running slow? Have you done any benchmarking? — MonkeyZeus, Aug 02 '16 at 18:42
This seems overly complicated; why not just return an associative array from the database? If you used PDO, you could even use `fetchAll` to dispense with the loop altogether. — miken32, Aug 02 '16 at 18:45
See [How can I prevent SQL-injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — A.L, Aug 02 '16 at 18:48
By using `select *` you are potentially pulling more data than necessary; just because you don't actually use it doesn't mean it won't be sent. Now imagine how much slower this might be if someone later adds player photos (as BLOBs) directly to the table. — Uueerdo, Aug 02 '16 at 18:54
Not sure why I got so many negative reviews... Anyway, I will check theses solutions and come back on this thread, thanks everyone. — CBinet, Aug 02 '16 at 20:19

Norbert · Answer 1 · 2016-08-02T19:56:26.937

3

You need to have an index om you EMAIL column to keep the speed up.

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

Create an index:

CREATE INDEX idx_nn_1 ON players(EMAIL);

Since your code is open to anybody using SQL injection go and read up on SQL injection and PHP PDO. Your code is even for SQL injection one of the easiest to hack (so really make this code better or you are in trouble).

edited Aug 02 '16 at 19:56

answered Aug 02 '16 at 18:41

Norbert

6,026
3
17
40

2

*I can do this for you* That's nice to warn users about security risks but suggesting to use them is not constructive. – A.L Aug 02 '16 at 18:45
1

@A.L Using some humour to get a point across.... – Norbert Aug 02 '16 at 18:46
3

@NorbertvanNobelen, +1 for the index recommendation. I also support warning people about security risks, and I support using humor, but *irony* is often hard for readers to recognize in a text-only medium. – Bill Karwin Aug 02 '16 at 19:02
2

@BillKarwin Removed the irony :) – Norbert Aug 02 '16 at 19:56

score 2 · Accepted Answer · answered Aug 02 '16 at 18:55

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));

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

2 Answers2