Explain analysis query

Briefly:

You can use the EXPLAIN or DESC keyword to simulate the optimizer executing SQL query statements to know how MySQL handles your SQL statements. This can help you analyze the performance bottlenecks of your query statement or table structure.

With the explain command you get:

1. Reading Order of Table

2. Operation type of table read operation

3. Which indexes can be used

4. Which indexes are actually used

5. References between tables

6. How many rows per table are queried by the optimizer

Why to use explain:

Explain can help us analyze SELECT statements and let us know why queries are inefficient, thus improving our queries and enabling the query optimizer to work better.

How the MySQL query optimizer works:

The MySQL query optimizer has several goals, but the primary goal is to use the index as much as possible, and use the most restrictive indexes to eliminate as many rows of data as possible.

The final goal is to commit the SELECT statement to find the data row, rather than excluding the data row.

The reason the optimizer tries to exclude rows of data is that the faster it excludes rows of data, the faster it is to find rows that match the criteria.

If you are able to perform the most rigorous tests first, queries can be executed faster.

Explain limitations:

1. Explain will not tell you about triggers, stored procedure information, or user-defined functions that affect the query.

2, explain will not consider the cache.

Explain analysis query