I assume that anyone who has ever solved the problem of why a query takes so long in MySQL knows and uses the EXPLAIN command .
If not, in short, the EXPLAIN command, among other things, is used to show us how MySQL processes the query.
I will use the MySQL shell for a simple demonstration. I have a vyboh4 database with a database for my test WordPress, I have the prefix set to netwp_. The first SELECT that WordPress performs after connecting to the database is in this case:
SELECT option_name, option_value FROM netwp_options WHERE autoload = ‘yes’
Since the output is not very clear, we write the lines vertically:
We can see that everything is fine, MySQL will use the ‘autoload’ index. So we cancel the index and perform EXPLAIN again:
As you can see at first glance
The only difference is that no index was used, but the output was limited by WHERE. That didn’t tell us much about speed. EXPLAIN is a very useful tool, especially if you perform operations on multiple tables. Documentation for the EXPLAIN command output is available on the official website.
So why am I even writing this? The answer is very simple. As of MySQL 8.0.18, MySQL has let us peek under the hood of the scheduler.
So let’s take a look at EXPLAIN ANALYZE , which will allow us to do this.
Same database, same query. Let’s start where we left off, we have a table without an index on the ‘autoload’ field.
We run EXPLAIN ANALYZE, add the ‘autoload’ index and run EXPLAIN ANALYZE again.
This is what it looks like:
So let’s explain.
If you don’t need to tune the performance of the server for a specific application and, for example, change the weights that the scheduler attaches to individual operations, then we can only use the following data: 0.114..0.860 The first number is the number of saudi arabia phone number data milliseconds needed to find the first record, the second to the last. In the second line, you can see that the MySQL server went through the entire table and it shows how much it cost.
After adding the index it looks like this:
EXPLAIN: -> Index lookup on netwp_options using autoload
at first glance you can see that it is faster video allows you to present testimonies and the “price” is much lower, and we are talking about a table with 227 rows.
I applaud Oracle for this step forward. Finally, we by lists have in our hands a powerful tool for analyzing SQL queries.
Another link to the official documentation
If you are a CZECHIA.com customer , you can find the MySQL database administration interface.