When you analyse a SELECT statement with the command EXPLAIN, MySQL shows information from the optimizer about the query execution plan. That is MySQL explains how the MySQL engine would execute the SELECT quesry.It will show the details about how the tables are joined and which will be the order of joining. EXPLAIN estimates of the number of rows that will need to be examined from each table.
Using the EXPLAIN command , you can decide where you should add indexes to tables to get a faster SELECT results that uses indexes to find rows in the table. We can use EXPLAIN command to check whether the optimizer joins the tables in an optimal order.
If you have a problem with indexes not being used when you believe that they should be, you should run ANALYZE TABLE to update table statistics such as cardinality of keys, which can affect the choices the optimizer makes.
MySQL resolves all joins using a single-sweep multi-join method. MySQL reads a row from the first table, and then finds a matching row in the second table, the third table, and so on. When all tables are processed, MySQL outputs the selected columns and backtracks through the table list until a table is found for which there are more matching rows.
The MySQL query optimizer has several goals, but its primary aims are to use indexes whenever possible and to use the most restrictive index in order to eliminate as many rows as possible as soon as possible.
