Query run time analysis in MySQL database?

Question

I am using a MySQL database with phpMyAdmin as an interface (I'm not sure if I have remote / client access). I have a script that queries a database and would like to know how long each request takes? What is the easiest way to do this? Can I install another PHP application on the server?

Answer 1

If you have access to the MySQL configuration files, you can enable general query log and slow query log .

See here for more details.

Since, as I think, 5.1.6 , you can also do this at runtime:

 SET GLOBAL long_query_time = 10 /* which queries are considered long */ SET GLOBAL slow_query_log = 1 

but try anyway, I don’t remember the exact version when it appeared.

Answer 2

For most of the applications I'm working on, I turn on query profiling output, which can be easily turned on in the development environment. This displays SQL, runtime, stack trace, and a link to output the explanation. It also highlights queries longer than 1 second.

Although you probably don't need something complicated, you can get a pretty good idea of ​​the query execution time by writing a function in PHP that completes the query and stores debugging information in the session (or just displays it). For instance:

 function run_query($sql, $debug=false, $output=false) { $start = microtime(true); $q = mysql_query($sql); $time = microtime(true) - $start; if($debug) { $debug = "$sql<br/>$time<br/><br/>"; if($output) { print $debug; } else { $_SESSION['sql_debug'] .= $debug; } } return $q; } 

This is just some gross idea. You can customize it as you wish.

Hope this helps -

Answer 3

You should set long_query_time to 1, since setting it to 10 excludes most, if not all of your queries. At the mysql prompt, type

set @@ long_query_time = 1;