How to Verify If the EverSQL MySQL Tool Provides Good Optimization Suggestions - 8 minutes - Lately in PHP Podcast Episode 93 Part 7

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. Previous Article: Make SQL Queries Run Faster Using the EverSQL MySQL Query Optimization Tool

Contents

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.

Show notes

• EverSQL
• MariaDB
• MySQL