Query time result in MySQL w/ PHP

Question

Is there a way that I can get the time of a MySQL query (specifically with PHP)? The actual time it took to complete the query, that is.

Something such as: Results 1 - 10 for brown. (0.11 seconds)

I tried to look for an example, to no avail. Here is an example of my code:

                    // prepare sql statement
                $stmt = $dbh->prepare("SELECT ijl, description, source, user_id, timestamp FROM Submissions WHERE MATCH (ijl, description) AGAINST (?)");

                // bind parameters
                $stmt->bindParam(1, $search, PDO::PARAM_STR);

                // execute prepared statement
                $stmt->execute();

For my current full text search using a MyISAM table engine. Any help would be incredible. Thank you.

Solution 1

$starttime = microtime(true);

//Do your query and stuff here

$endtime = microtime(true);
$duration = $endtime - $starttime; //calculates total time taken

NOTE that this will give you the run time in seconds(not microseconds) to the nearest microsecond due to get_as_float parameter being true. See this

Solution 2

this may be help you

http://dev.mysql.com/doc/refman/5.0/en/show-profile.html

mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE T1 (id INT);
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW PROFILES;
+----------+----------+--------------------------+
| Query_ID | Duration | Query                    |
+----------+----------+--------------------------+
|        0 | 0.000088 | SET PROFILING = 1        |
|        1 | 0.000136 | DROP TABLE IF EXISTS t1  |
|        2 | 0.011947 | CREATE TABLE t1 (id INT) |
+----------+----------+--------------------------+

greetings

Solution 3

There are two possibilities I can tell you now:

• wrap ->execute() with microtime() and measure it yourself, possibly wrapping whole "querying" code snippet within a class / function
• run EXPLAIN query of that query and see if you can read some values from the returned data

Hope that helps.

Comments

[bob_cobb]

Thanks, it does. Is EXPLAIN giving the actual time it took to complete, or just an estimate of what it will take? Also, would it be less overhead to let PHP handle it and make a counter (like rayman suggested above)?

[Tomasz Kowalczyk]

The PHP solutions gives you total time that took to complete such task, but the SQL solution gives you exact time that was consumed by database while preparing target dataset.

[erdomester]

The problem with this is that when I get 0.1 as a result this means the query ran for 0.000001 second which is a bit too fast

[Ehsan88]

@erdomester this is because of the error in the answer. microtime(true) returns seconds not microseconds. I've edited the answer.

[Stackoverflow]

we are in MySQL 8, how we can get the "real time of any QUERY" ?

[Jinxmcg]

MYSQL 8 has the same query profiling dev.mysql.com/doc/refman/8.0/en/show-profile.html

[Stackoverflow]

Thanks @Jinxmcg, when I run SET profiling = 1; I get Warning: #1287 '@@profiling' is deprecated and will be removed in a future release. then what is the "NEW" command/method ?

[Jinxmcg]

I have not used it, however I think performance schema is what you should look into: dev.mysql.com/doc/refman/8.0/en/performance-schema.html

[Aurangzeb]

This is the right answer imo as it uses the time calculated by mysql instead of re-calculating time again on a php like others are doing