PHP Classes

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

Recommend this page to a friend!
  Blog PHP Classes blog   RSS 1.0 feed RSS 2.0 feed   Blog Make SQL Queries Run ...   Post a comment Post a comment   See comments See comments (0)   Trackbacks (0)  

Author:

Viewers: 259

Last month viewers: 5

Categories: PHP Tutorials, Lately in PHP Podcast

Slow SQL queries are one of the factors that can make sites slower and cause harm to the user experience.

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.




Loaded Article

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

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


Listen or download the podcast, RSS feed and subscribe in iTunes

Click on the Play button to listen now.

Introduction music obtained with permission from: http://spoti.fi/NCS

View Podcast in iTunes

Listen on Spotify
Listen on Spotify


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.

Watch the podcast video

See the Lately in PHP podcast play list on YouTube and Subscribe to this channel there.

Episode 93 Part 6 Video

Play Video

What was said in the podcast

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.

EverSQL SQL query optimization form with the SQL query to optimize

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




You need to be a registered user or login to post a comment

1,616,375 PHP developers registered to the PHP Classes site.
Be One of Us!

Login Immediately with your account on:



Comments:

No comments were submitted yet.



  Blog PHP Classes blog   RSS 1.0 feed RSS 2.0 feed   Blog Make SQL Queries Run ...   Post a comment Post a comment   See comments See comments (0)   Trackbacks (0)