top of page

Latest MySQL Statistics for Slow Queries

To log slow query is the important longstanding technique of footage slow query, so the database administrator can control which queries are in the utmost need for optimization. Since MySQL 5.6, it must some outspread been outshined by the Routine Schema which has minor directly above and thus allows gathering statistics about all requests. The slow query log has one main benefit though: the data is persevered. In MySQL 8.0.14 which was newly out, there is an development for the slow query log: extra statistics about the logged requests.

The additional info is not logged by default. To allow the feature, allow the log_slow_extra choice:

mysql> SET PERSIST log_slow_extra = ON;

Here, SET PERSIST is castoff, so the arrangement change is kept on when MySQL is resumed. If you just need to try the feature, you can custom SET GLOBAL, then choose later whether you need to keep it allowed.

That is all that is essential. You can now complete a “slow” query and yield a look at the logged data. An informal way to accomplish a slow query is to perform DO SLEEP(...) where you can substitute ... with the number of instants you want to snooze. However, that is not a very thought-provoking request. Additional option is to inferior long_query_time to confirm your query is captured If you usual long_query_time to zero, all requests are logged:

Now SET SESSION is used so only the existing connection is artificial.

Finally, execute a query:

The resultant log slow query best is:

The additional data can be a slight solid to see due to the organizing of the blog – the additional fields are:

As judgement, here is the info for the same request with log_slow_extra = OFF:


10 views0 comments

Recent Posts

See All

What are the future prospects of Java

According to a survey conducted, nearly 63% programmers mentioned that they will likely continue to work with Java along with coding with Python, SQL and HTML/CSS. In addition, the giants in the corpo

Deleting Duplicate Rows in MySQL

In this blog, you will study several ways to delete duplicate rows in MySQL. In this blog, I have shown you how to find duplicate values in a table. Once the duplicates rows are recognized, you may ne

Upload Data to MySQL tables using mysqlimport

Uploading quite a lot of rows of data from a text, csv, and excel file into a MySQL table is a repetitive task for sysadmins and DBAs who are handling MySQL database. This blog clarifies 4 applied exa


bottom of page