How do I test the speed of a mySQL query?

Question

I have a select and query like below...

    $sql = "SELECT * 
        FROM notifications 
        WHERE to_id='".$userid."' 
            AND (alert_read != '1' OR user_read != '1') 
        ORDER BY alert_time DESC";
    $result = mysql_query($sql);

how do I test how long the query took to run?

Answer 1

There is a MySQL system variable profiling which is created in the INFORMATION_SCHEMA database for your particular session. See the code below:

mysql> set profiling=1;

Just simply execute the query... the query's execution session will be stored in the INFORMATION_SCHEMA.

mysql> select count(*) from client where broker_id=2;

Once the query is completed, just execute this line:

mysql> show profiles;

which shows entire execution time of the queries. Just you execute and tell the query ID like below to get execution/speed of your query.

mysql> show profile for query 1;