Author: Manuel Lemos
Updated on: 2022-10-25
Posted on: 2022-10-25
Viewers: 266 (October 2022 until August 2023)
Last month viewers: 2 (August 2023)
Categories: PHP Tutorials, Lately in PHP Podcast
The MySQL slow query log file provides a list of slow queries as they happen. So you need to monitor that file to determine the most critical slow SQL queries you need to optimize first.
Read this article, watch a 2-minute video, or listen to part 3 of episode 93 of the Lately in PHP podcast to learn how to use a free tool to monitor the MySQL slow query log and find the slowest SQL queries.
In this article you can learn:
How to Improve the Speed of An Application that Uses a MySQL Database Server
1. Previous Article: Find MySQL Slow Queries by Activating the Slow Query Log
2. Previous Article: Find MySQL Slow Queries in a Production Server by Activating the Slow Query Log
3. This Article: How to Use Simple MySQL Monitoring Tools for Free to Find the Slowest Queries that You Should Optimize First
4. Next Article: How to Use EverSQL SQL Query Optimizer Tool to Optimize Specific SQL Queries
Download Size: 2MB Listeners: 621
Introduction music obtained with permission from: http://spoti.fi/NCS
Sound effects obtained with permission from: https://www.zapsplat.com/
In iTunes, use the Subscribe to Podcast... item of the Advanced menu, and then enter the URL above to subscribe to this podcast.
See the Lately in PHP podcast play list on YouTube and Subscribe to this channel there.
Episode 93 Part 3 Video
Monitor the MySQL Slow Query Log to Find the Most Critical Slow SQL Queries to Optimize First
Now that we have the slow query log enabled, you need to monitor that slow query log.
How can we monitor this slow query log?
You can use this simple command in the Linux system called the tail.
And in this case, it gets a parameter to get only the last hundred lines. So you want to check only the latest queries that are slow.
mlemos@development:~> tail -100 /var/log/mysql/mysqld_slow.log
mlemos@development:~> tail -100 /var/log/mysql/mysqld_slow.log #. #. #. # Time: 220904 21:35:04 # User@Host: mlemos[mlemos] @ localhost  # Thread_id: 18289 Schema: phpclasses QC_hit: No # Query_time: 538.469179 Lock_time: 0.000132 Rows_sent: 29941 Rows_examined: 3403486 # Rows_affected: 0 Bytes_sent: 5264934 SET timestamp=1662352504; SELECT access.post AS post, blog.type AS type, access.date AS date, access.subscriber AS subscriber, access.ip AS ip, access.agent AS agent, access.id AS id, post.posted AS posted, post.author AS author FROM blog_post_view access, post, blog WHERE access.processed='N' AND access.date<'2022-09-01' AND access.post=post.id AND post.blog=blog.id ORDER BY date; #. #. #. mlemos@development:~>
What is important here is that this query took 538 seconds. That is a lot. It's almost 10 minutes the user is waiting, or maybe some process running in the background is being slowed down by a slow query.
So this query needs to be optimized. As you may see, it shows that 29 thousand lines, more than that, was the number of rows that the query sent to the script or application you are running.
And then it also shows that it queried over 3 million rows of the database table. This fact means that the database server examined all those lines, which explains why this query is slow.
So below here is the actual query; this is important because you need to know what query you need to optimize.
You need to be a registered user or login to post a comment
Login Immediately with your account on:
No comments were submitted yet.