View MySQL statement run time

In order to verify the efficiency of the Select 1 and select 1 from TableName with the select * from TableName, it is necessary to test the respective execution time. So, let's summarize how the MySQL statement runs in a minute.

Method one: Show profiles.

1. Show Profiles is added after 5.0.37, to use this feature, make sure that the version is after 5.0.37.

Query Profiler is a query diagnostic analysis tool that comes with MySQL to analyze where the performance bottlenecks of an SQL statement are. Usually we are using the explain, and slow query log can not be accurately analyzed,

However, query Profiler can locate the various resource consumption of an SQL statement, such as Cpu,io, and the time it takes to execute the SQL.

View Database version method: Show variables like "%version%"; or select version ();

2. after confirming that the show profile is supported, check that the profile is turned on and the database is not turned on by default. The variable profiling is a user variable and must be re-enabled each time.

View method: Show variables like "%pro%";

Set Open method: Set profiling = 1;

Check out the show variables like "%pro%" again; It's already in the open state.

3. You can start executing some SQL statements that you want to analyze, and show profiles after execution, tosee the total execution time for all SQL.

Show profile for query 1 to see the time-consuming details of each operation performed by the 1th SQL statement.

Show profile CPU, block Io, memory,swaps,context switches,source for query 6; You can see the various resource consumption of a SQL statement, such as Cpu,io, etc.

Show profile all for query 6 view all the execution information for the 6th statement.

When the test is complete, close the parameters:

Mysql> Set profiling=0

Method Two: Timestampdiff to see the execution time.

One thing to note about this approach is that three SQL statements should be executed as much as possible, otherwise the error is too large to be allowed at all.

Set @d=now (); select * from Comment;select Timestampdiff (Second,@d,now ());

If it is done with the command line, one thing to note is that in select Timestampdiff (Second,@d,now ()), you must copy a blank line, or the last SQL will have to press ENTER to execute, so it is not allowed.

View MySQL statement run time