Analyze execution time of queries on MySQL database?

Question

I am using a MySQL database with myPHPadmin as the frontend (I am not sure I have remote/client access yet). I have a script that queries the database and would like to see how long each query takes? What is the easiest way to do this? Could I install another PHP app on the server?

Thanks!

Answer 1

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

See here for details.

Since, as I think, 5.1.6, you can also do it in runtime:

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

, but try it anyway, I don't rememeber exact version when it appeared.

Answer 2

For most applications that I work on, I include query profiling output that can easily be turned on in the development environment. This outputs the SQL, execution time, stack trace and a link to display explain output. It also highlights queries running longer than 1 second.

Although you probably don't need something as sophisticated, you can get a fairly good sense of run time of queries by writing a function in PHP that wraps the query execution, and store debug information on the session (or simply output it). For example:

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;

 }

That's just kind of a rough idea. You can tweak it however you want.

Hope that helps -

Answer 3

You should set long_query_time to 1 since setting it to 10 will exclude most if not all of your queries. In the mysql prompt type

set @@long_query_time=1;