Author: Manuel Lemos
Updated on: 2023-03-17
Posted on: 2022-12-21
Viewers: 149 (December 2022 until August 2023)
Last month viewers: 2 (August 2023)
Categories: PHP Tutorials, Lately in PHP Podcast
The actual gains in performance may vary depending on the type of query. Significant SQL query optimizations reduce the number of table rows scanned when the MySQL server executes a query.
Read this article, watch an 8-minute video, or listen to part 7 of episode 93 of the Lately in PHP podcast to learn how to verify the efficiency of the MySQL query optimizations suggested by the EverSQL tool.
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. Previous Article: Make SQL Queries Run Faster Using the EverSQL MySQL Query Optimization Tool
7. This Article: How to Verify If the EverSQL MySQL Tool Provides Good Optimization Suggestions - 8 minutes - Lately in PHP Podcast Episode 93 Part 7
8. Next Article: How to Implement PHP Asyncronous programming using PHP 8.1 Fibers - Lately in PHP Podcast Episode 94
Download Size: 2MB Listeners: 1045
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 7 Video
1. How to Analyse The MySQL EXPLAIN Query Results
You may wonder how I can be certain that this optimization really works for your database tables?
You need some do some tests to figure the details of the query before and after the optimization so you can compare what happened and conclude if there was really progress.
To figure what is the the exact state of the database tables in terms of indexes and maybe other aspects that may need to be optimized you can use the explain command.
So here I'm back to the terminal console to execute a specific query. So it's the same query that we want to optimize, but you need to proceed with explain SQL command.
EXPLAIN SELECT post.subject AS subject, post.summary AS summary, post.posted AS posted, post.postnumber AS post, post.body AS body, post.css AS css, post.forum AS forum, post.content_type AS content_type, blog.id AS blog, post.id AS id, blog.type AS blog_type, post.featured AS featured, post.slug AS slug, post.updated AS updated, post.snippet AS snippet, post.live AS live, post.canonical_link AS canonical_link FROM blog, post WHERE blog.title='Package 11831 blog' AND post.blog=blog.id AND post.postnumber=1 AND post.status='P';
This SQL command is specific to the MySQL database. It's not standard. You can use it to analyze the query, so it can tell you the details about how the database server will perform this query.
Once you execute this command, you get a table with results. It's not a real database table. It's just a way to format the result of the this analysis that the MySQL database server performs.
You can see several columns and several rows. Each row has information about each table that is used in the query. So we have three tables: blog post and access.
So when you execute this query, these tables are inspected and also have other aspects as columns that show you the details of how each table was inspected.
So here you have in this column possible keys.
These are the database server indexes that already exist, indexes, or keys, I mean, the primary keys.
And you can take a look, and it shows exactly that exist. In the key column, it shows the keys that were really used to perform this query. So you have possible keys and real keys that were used. Here you can evaluate the potential of optimization that was used or used or not.
And next, you have another column that says the table columns that were used in the query that was as reference to perform joins. Then you have an important column that says rows. This is the number of database table rows that were performed for in that were inspected in the the query for each table that is used.
So for the blog table, you can see 564 rows were inspected and then 2 rows for the post table and 1004 rows for the access table.
Finally, you have another column called extra. And this column shows if the indexes were used or not and how. You don't need to really understand this when you use a tool like the SQL Optimizer of EverSQL because it will do the work of analysis.
2. Perform the Database Table Alteration Queries Suggested by The EverSQL Tool
Next, you are really going to perform the suggested alterations in a previous step. So you have here the SQL queries. And here I have some commands to perform the table alterations.
mlemos@development:~> mysql -p -u mlemos phpclasses -e 'ALTER TABLE `blog_post_view` ADD INDEX `blog_post_view_idx_processed_post_date` (`processed`,`post`,`date`);' Enter password: mlemos@development:~> mysql -p -u mlemos phpclasses -e 'ALTER TABLE `post` ADD INDEX `post_idx_id_blog` (`id`,`blog`);' Enter password: mlemos@development:~>
3. Compare The Performance of the Query Before and After the Optimization
Now you are going to do a final step which is to evaluate the result of the optimization.
Again you use the explain command using the same query, and then you have another table which is similar to the other one. The difference is what happens after the optimization.
Again the most important column is here: the rows. The rows tell you how many database table rows were inspected.
So if all goes well in the optimization effort, the number of rows was minimized. So as you may see here, the post and blog tables had only one row inspected.
Before you had two rows of the post table and 1004 rows inspected on the access table, and 564 on the blog table. So there was a change here mainly in the blog table and post table.
4. Conclusion About the MySQL Query Optimization Effort
So this is good and this is the evidence that you need to confirm that there was a really good effort as an optimization.
Maybe you can optimize further, but for now what is important is to show you that this really works.
You need to be a registered user or login to post a comment
Login Immediately with your account on:
No comments were submitted yet.