How to view the execution time of SQL statements in MySQL

How to view the execution time of SQL statements in Mysql

1. Initial SQL Preparation

Initialization table

-- User table create table t_users(
                        id int primary key auto_increment,
-- Username varchar(20),
-- Password password varchar(20),
-- Real name real_name varchar(50),
-- Gender 1 means male 0 means female sex int,
--birth date,
-- Mobile phone number mobile varchar(11),
-- The uploaded avatar path head_pic varchar(200)
);

Initialize Data

--Add user data insert into t_users values(null,'whj','123456','王恒杰',1,NOW(),'12345678901','boy.jpg');
insert into t_users values(null,'dzw','123456','邓正武',1,NOW(),'12345678901','boy.jpg');
insert into t_users values(null,'yfj','123456','杨福君',1,NOW(),'12345678901','girl.jpg');
insert into t_users values(null,'zx','123456','张西',1,NOW(),'12345678901','girl.jpg');
insert into t_users values(null,'zxj','123456','周宣君',0,NOW(),'12345678901','boy.jpg');
insert into t_users values(null,'lfk','123456','刘福昆',1,NOW(),'12345678901','boy.jpg');

Table Structure

Related data

2. Mysql checks the execution time of Sql statements

1. show profiles;

First enter show profiles. There is no data at this time

2. show variables; show varables: Check whether profiling is turned on, that is, Value is ON

Directly using the show variables command will display all variables. There are too many variables for us to view.

You can use fuzzy query and use like to select profiling

Generally, it is off if it is not enabled.

 show variables like 'profiling';

3. set profilling=1 to enable profiling

show variables like 'profiling';

In this way, our Mysql can view the execution time of the Sql statement

3. Execution time of different queries

select * from t_users;     
select id,username,password,real_name,sex,birth,mobile,head_pic from t_users;
 
select * from t_users where username like 'whj';

Time comparison of three queries

in conclusion:

When using SELECT statements or other statements, there is a big difference in performance between using * directly and adding all fields to the query, so we usually write queries with fields.

Summarize

This is the end of this article about how to view the execution time of SQL statements in Mysql. For more information about how to view the execution time of SQL statements in Mysql, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!