Why does my AJAX GET request take 2-3 seconds when the same MYSQL command line query takes less that 0.1 second?

Question

From my perspective the code looks fairly efficient, so why does it take over 2 seconds for the PHP to load?

Test server, No load

Versions:

Apache/2.4.7 (Ubuntu)

PHP/5.5.9-1ubuntu4.20

MYSQL 5.5.53-0ubuntu0.14.04.1 (Ubuntu)

Firefox 50.0

Chromium 53.0.2785.143

---

From mysql command line:

SELECT company, customer_number, invoice_date, invoice_number, invoice_rep, item_number, item_description, item_qty, item_price FROM customer_data.invoices WHERE customer_number = "047811" ORDER BY invoice_date, invoice_number, item_number;

39 rows in set (0.01 sec)

---

AJAX request

Firefox 50.0 * waiting 2191 ms

Chromium 53.0.2785.143 * waiting 2193 ms

javascript function:

function show_invoices(customer_number){
document.body.style.cursor = 'wait';
document.getElementById('float_panel1_body').innerHTML = "<h2 class='ticket_yellow'>Searching...</h2>";

var link = "http://ls1/portal/include/customer_data/CustomerData_invoices.php?customer_number=" + customer_number;
var xhttp = new XMLHttpRequest();

xhttp.open("GET", link, true);

xhttp.onreadystatechange = function() {
    if (xhttp.readyState == 4 && xhttp.status == 200) {
        search_results_raw = xhttp.responseText;

        if (search_results_raw == "false") {
            //document.getElementById('float_panel1_header').innerHTML = xhttp.responseText;
            //show_no_results();
        }else{
            //document.getElementById('float_panel1_header').innerHTML = xhttp.responseText;
            //show_results_invoices(xhttp.responseText);
            test_results(xhttp.responseText);
        }
    }
}
xhttp.send();

}

PHP code: (CustomerData_invoices.php)

<?php
$cd_invoices = [];
$user="user";
$host="localhost";
$password="password";
$database="customer_data";

if ( !isset($_GET['customer_number']) ) {
    exit();
}
$customer_number = $_GET['customer_number'];

if (!$cxn = mysqli_connect($host,$user,$password,$database)) {
    $error = "SQL error in connecting to server. ";
    $error = $error.mysqli_error($cxn);
    echo "<strong>$error</strong>";
    //include 'backtohome.php';
    exit();
}
/// this is where the text is escaped for mysql
/// after mysqli_connect, but before mysqli_query
$customer_number_escaped = mysqli_real_escape_string($cxn, $customer_number);

// now setup query with escaped strings
$query="SELECT company, customer_number, invoice_date, invoice_number, invoice_rep, item_number, item_description, item_qty, item_price FROM customer_data.invoices WHERE customer_number = $customer_number_escaped ORDER BY invoice_date, invoice_number, item_number";

if (!$result = mysqli_query($cxn,$query)) {
    $error = "SQL error in query. ";
    $error = $error.mysqli_error($cxn);
    echo "<strong>$error</strong>";
    exit();
}

$returned = mysqli_affected_rows($cxn);
if ($returned > 0) {
    // record exist
    // temp change to array
    while($row = mysqli_fetch_assoc($result))
    {
        $cd_invoices[] = $row;
    }
    mysqli_close($cxn);
    echo json_encode($cd_invoices);
}else{
    echo "false";
}

?>

Answer 1

The MySQL query is not the only thing that is taken into account when determining execution time. It also depends on the other parts of your script and your latency to the server. Taken from Tracking the script execution time in PHP, you can use the following code to determine the execution time of your script:

// Script start
$rustart = getrusage();

// Code ...

// Script end
function rutime($ru, $rus, $index) {
    return ($ru["ru_$index.tv_sec"]*1000 + intval($ru["ru_$index.tv_usec"]/1000))
     -  ($rus["ru_$index.tv_sec"]*1000 + intval($rus["ru_$index.tv_usec"]/1000));
}

$ru = getrusage();
echo "This process used " . rutime($ru, $rustart, "utime") .
    " ms for its computations\n";
echo "It spent " . rutime($ru, $rustart, "stime") .
    " ms in system calls\n";