Make SQL Queries Run Faster Using the EverSQL MySQL Query Optimization Tool - 4 minutes - Lately in PHP Podcast Episode 93 Part 6

In this article you can learn:

1. Previous Article: Find MySQL Slow Queries 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

Contents

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-lp152.2.8.1.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.

Show notes

• EverSQL
• MariaDB
• MySQL