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.

Dwayne

Just to mention that the time returned is in units of seconds, not microseconds, so a figure of e.g. 0.0412356 is around a twenty-fifth of a second, not 4 x 10 exp -8 . – Devin

Answer 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

Saccharin

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 – Giamo

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

Answer 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

Overweening

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 – Marasco

Answer 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.

Oviform

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)? – Dwayne

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. – Oviform

Answer 4

If you are using MYSQL 5, you should better check SHOW PROFILE

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

and print the timings in php...or EXPLAIN the SQL statement which took longer or detail each query...by CPU etc

Highhanded

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

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

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 ? – Livraison

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 – Highhanded