How do I display execution time of a MySQL query in PHP?

Question

I am working on a PHP application that takes queries in a text box and returns paginated results. As part of the application I want to report the running time of the query.

Here is what I have done so far.

I started off by enabling profiling in by directly entering in the text box and running the script:

set global profiling = 1

Using the provided text box I enter the following query:

select @@profiling

And get:

Finally, I run the query as so:

select * from log

However, when I run the command for to profile the query:

show profiles

I receive no result and no content displayed on the page.

Since I see no table after the command "show profiles" does this mean that there are not sufficient privileges or am I missing another step?

I followed the procedure on:

Measuring actual MySQL query time

Please advise.

My PHP code is as follows:

<?php
    if($_POST)
    {
        $db = new PDO('mysql:host=localhost;dbname=<dbname>;charset=utf8', 'user', 'pass');
        $stmt = $db->prepare($_POST['query']);
        $stmt->execute();

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

        $errmsg = $stmt->errorInfo()[2]; //Output the error message - Index 2 of the array

        echo $errmsg;
    }  
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>
<title>Log</title>
</head>
<body>
    <form method="post" id="queryform">
        <div class="label">
            <span class="label">Enter SQL Query</span>
        </div>
        <div class="input">
            <input type="text" name="query" size="150" value="<?=$_POST['query']?>" />
        </div>
    </form>
    <? if (isset($records)): ?>
    <table border="1">
        <? foreach($records as $record): ?>
            <tr>
                <? foreach($record as $colname => $value): ?>
                    <td>
                       <?=$value;?>
                    </td>
                <? endforeach; ?>    
            </tr>
        <? endforeach; ?>
    </table>

    <? endif; ?>
</body>
</html>

Any help would be appreciated.

Community · Accepted Answer · 2017-05-23 11:44:05Z

This worked like a charm!

    $db->query('set profiling=1'); //optional if profiling is already enabled
    $db->query($_POST['query']);
    $stmt = $db->query('show profiles');
    $db->query('set profiling=0'); //optional as well

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

    $errmsg = $stmt->errorInfo()[2]; //Output the error message

UPDATE (The following now works on innodb on my present setup)

$db->query('set profiling=1'); //optional if profiling is already enabled
$db->query($_POST['query']);
$res = $db->query('show profiles');
$records = $res->fetchAll(PDO::FETCH_ASSOC);
$duration = $records[0]['Duration'];  // get the first record [0] and the Duration column ['Duration'] from the first record

Result of (show profiles) from phpmyadmin.

Query_ID    Duration    Query   
1           0.00010575  SELECT DATABASE()

Getting the actual (absolute) execution time of the last query in PHP (excluding network latency etc)

marcvangend · Accepted Answer · 2016-04-12 08:43:48Z

6

How about this:

$start = microtime(true);
$stmt->execute();
$end = microtime(true);
echo "This query took " . ($end - $start) . " seconds.";

edited Apr 12, 2016 at 8:43

marcvangend

5,6524 gold badges24 silver badges33 bronze badges

answered Apr 30, 2014 at 0:16

Alan P.

3,1537 gold badges34 silver badges60 bronze badges

3 Comments

Vahe Over a year ago

Thank you for the response. Is this the only way that would work? I did that method not thinking that it would perhaps be the only working solution. I appreciate your help.

Marcio Mazzucato Over a year ago

The right message should be "This query took " . "($end - $start) . " seconds."

marcvangend Over a year ago

@MarcioMazzucato you are right, microtime(true) returns a float in seconds. I edited the answer accordingly.

Collectives™ on Stack Overflow

How do I display execution time of a MySQL query in PHP?

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related