PHP While Loop with MySQL Has Inconsistent Execution Time, How Can I Fix This?

Question

My problem is simple. On my website I'm loading several results from MySQL tables inside a while loop in PHP and for some reason the execution time varies from reasonably short (0.13s) or to confusingly long (11s) and I have no idea why. Here is a short version of the code:

<?php
    $sql = 
    "SELECT * FROM test_users, image_uploads 
        WHERE test_users.APPROVAL = 'granted' 
        AND test_users.NAME = image_uploads.OWNER
        ".$checkmember."
        ".$checkselected."
        ORDER BY " . $sortingstring . " LIMIT 0, 27
    ";

$result = mysqli_query($mysqli, $sql);

$data = "";
$c = 0;

$start = microtime(true);

while($value = mysqli_fetch_array($result)) {
    $files_key = $value["KEY"];
    $file_hidden = "no";    

    $inner_query = "SELECT * FROM my_table WHERE KEY = '".$files_key."' AND HIDDEN = '".$file_hidden."'";
    $inner_result = mysqli_query($mysqli, $inner_query);
    
    while ($row = mysqli_fetch_array($inner_result)) {
        // getting all variables with row[n]
    }

    $sql = "SELECT * FROM some_other_table WHERE THM=? AND MEMBER=?";
    $fstmt = $mysqli->prepare($sql);
    $fstmt->bind_param("ss", $value['THM'], 'username');
    $fstmt->execute();
    $fstmt->store_result();
    if($fstmt->num_rows > 0) { 
        $part0 = 'some elaborate string'; 
    } else {
        $part0 = 'some different string'; 
    }
    $fstmt->close();
    
    // generate a document using the gathered data
    include "../data.php"; // produces $partsMerged
    
    // save to data string
    $data .= $partsMerged;
    $c++;
}

$time_elapsed_secs = substr(microtime(true) - $start, 0, 5);
// takes sometimes only 0.13 seconds
// and other times up to 11 seconds and more
?>

I was wondering where the problem could be.

Does it have to do with my db connection or is my code flawed? I haven't had this problem at the beginning when I first implemented it but since a few months it's behaving strangely. Sometimes it loads very fast other times as I said it takes 11 seconds or even more.

How can I fix this?

wait a second. I've shortened this query I'll post the full unedited one.. — Miger, Nov 12 '20 at 10:15
I don't think its a good idea to do a `sub query` inside your `while` loop, just do a `main` query, and manipulate the arrays in `php` instead of doing a `sub query` each time in your `while` loop! — Burhan Kashour, Nov 12 '20 at 10:15
Are you running it in a browser (i.e. through a web server) or through a CLI? — Andy, Nov 12 '20 at 10:15
I suggest you to learn how to use `JOIN`s. I'm sure your result can be get with only 1 query — Cid, Nov 12 '20 at 10:17
@Ingus its not about `SELECT * FROM`, the usage of the server ram will be so high when doing this amount of `queries` each time you load the page, he must do the `query` one time instead of doing it inside the `while` loop — Burhan Kashour, Nov 12 '20 at 10:19
You should post values for things such as `$limitbegin` and `$cardmax`. It depends what those values are as to how many records it's fetching in the first query. Also...please answer the question on the environment you're running this in - web server or CLI — Andy, Nov 12 '20 at 10:19
So you all think when I change this code around, get rid of the while loop and use a query instead it will not have loading issues anymore? — Miger, Nov 12 '20 at 10:23
If the SQL executes in the same amount of time through a MySQL command prompt, the issue is likely not with the query but with your web server or connection to it. See my answer for further info. You haven't tried executing the raw SQL as far as I can tell. You're doing it through a script which is being run via a web server. There's a myriad of things in that chain where the bottleneck may be. — Andy, Nov 12 '20 at 10:24
By the way, using this syntax `SELECT * FROM a, b WHERE a.id = b.something` isn't the standard anymore in writing joins since about... 28 years — Cid, Nov 12 '20 at 10:32
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Nov 12 '20 at 11:22

Andy · Accepted Answer · 2020-11-12T10:32:37.473

There's a few ways to debug this.

Firstly, any dynamic variables that form part of your query (e.g. $checkmember) - we have no way of knowing here whether these are the same or different each time you're executing the query. If they're different then each time you are executing a different query! So it goes without saying it may take longer depending on what query is being run.

Regardless of the answer, try running the SQL through the MySQL command line and see how long that query takes.

If it's similar (i.e. not an 11 second range) then the answer is it's nothing to do with the actual query itself.

You need to say whether the environment you're running this in is a web server, e.g. accessing the PHP script via a browser, or executing the script via a command line.

There isn't enough information to answer your question. But you need to at least establish some of these things first.

The rule of thumb is that if your raw SQL executes on a MySQL command line in a similar amount of time on subsequent attempts, the problem area is elsewhere (e.g. connection to a web server via a browser). This can be monitored in the Network tab of your browser.

I will debug this problem further.. It might just be my internet connection. This doesn't really sense though since it actually loads very fast sometimes (only 0.13s) but rarely nonetheless. I've also checked my internet speed and its around 450 Mbps so I don't think the problem is there. Still if I go onto the website with mobile data from my phone it loads fast every time.. I'm not sure what the problem is. Even though my programming is sloppy I don't think using the queries within the while loops is causing the issues I have.. — Miger, Nov 13 '20 at 11:23
and about the uncertainty if the query is consistent or not. I'm always loading approximately the same amount of data, sometimes the exact same requests can differ in loading times but its always either around 0.1s or then on the other hand is is around 10s there is not really much in between. — Miger, Nov 13 '20 at 11:27

PHP While Loop with MySQL Has Inconsistent Execution Time, How Can I Fix This?

1 Answers1