Author: Manuel Lemos
Updated on: 2022-12-21
Posted on: 2022-11-30
Viewers: 207 (November 2022)
Categories: PHP Tutorials, Lately in PHP Podcast
Fortunately, optimization tools can suggest small changes in your database schema that can make your SQL queries run much, so your Web sites can provide a much better user experience.
EverSQL is one of those optimization tools that can suggest effective changes for databases stored in MySQL servers or some other server compatible with MySQL, such as MariaDB.
Read this article, watch a 4-minute video, or listen to part 6 of episode 93 of the Lately in PHP podcast to learn how to optimize your MySQL database using the EverSQL tool for free.
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
5. Previous Article: How to Help the EverSQL Tool Optimizing MySQL SQL Queries More Efficiently by Passing It Your MySQL Database Metadata
6. This Article: Make SQL Queries Run Faster Using the EverSQL MySQL Query Optimization Tool
Download Size: 1MB Listeners: 669
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 6 Video
Make SQL Queries Run Faster Using the EverSQL MySQL Query Optimization Tool
1. Configure the EverSQL SQL Optimization Tool with the Database Server Type and Version
Below you have a continue button. Click on the continue button and then move on to the result of the optimization. Then you get back to the dialogue in the previous screen, and you will be ready to provide some details about the query.
1.1. Get the MySQL Database Server Type
First, you need to tell what is the type of MySQL database. There are many types here. There is the original MySQL database that is currently from Oracle. There are other compatible database servers from many providers.
You just need to figure which one you are using in your application, as well the version number. In this case, since I'm using MariaDB.
1.2. Get the MySQL (MariaDB) Database Server Version
I need to perform some steps to figure the the MariaDB database server version number. So, in this case, I use MariaDB in the OpenSuSE Linux. I just ran this command to the figure, which is the database server version.
rpm -q -a | grep mariadb- | grep -v client | grep -v errormessages
In your system, if you use some system that is not Linux or not OpenSuse, you need to figure the exact commands that you need to perform. Since there are many, I'm not going into details here. You can ask me at the end of this podcast episode, what could be the commands to figure out for that specific version in this case.
Since I use openSuSE Linux, I use their rpm command to get all packages and then filter by MariaDB and exclude client and errormessage, so I can only get as a result the MariaDB database server package. So as you may see here MariaDB is installed on the 10.4 version.
mlemos@development:~> rpm -q -a | grep mariadb- | grep -v client | grep -v errormessages mariadb-10.4.17-lp220.127.116.11.x86_64 mlemos@development:~>
This is the information that we need to move on and put here. In this form, you can specify other versions if you use other versions and other compatible database servers and then also the version number.
2. Enter the SQL Query That You Want to Optimize
Then you move on to the actual query. So the query that you figured before in the previous step. You can copy it here. There is this select that is slow.
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:~>
And you move on to this dialog and paste it here. Okay. The query is here. It is long. It does not fit the whole dialog window. So it wraps around in this case.
3. Request that the EverSQL Tool Suggests Database Changes to Make the SQL Query Run Faster
And you move on to the next step. The next step is actually performing the optimization. So below, you have a continue button. Click on the Continue button and then move on to the result of the optimization.
So you here see some ALTER commands that you need to perform to change your database structure to use some indexes that are recommended by the tool. And after that, your database table is optimized.
ALTER TABLE `blog` ADD INDEX `blog_idx_title_id` (`title`,`id`); ALTER TABLE `post` ADD INDEX `post_idx_postnumber_status_blog` (`postnumber`,`status`,`blog`);
4. Next: How to Test If the Database Changes Suggested by EverSQL Tool Improve The Query Execution Speed
In the next part of this episode of the Lately in PHP Podcast, I will show you how you can test if the database alteration queries lead to speed improvements in the database query that we want 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.