content_image/772518.jpg content_image/772531.jpg content_image/772530.jpg
<img src='content_image/772518.jpg'>

This is not perfect, but is much better: The product of the rows  values is less by a factor of 74. This version executes in a couple of seconds. 

A second alteration can be made to eliminate the column length mismatches for the tt.AssignedPC = et_1.EMPLOYID  and tt.ClientID = do.CUSTNMBR  comparisons: 

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15), MODIFY ClientID VARCHAR(15); 

After that modification, EXPLAIN  produces the output shown here: 

<img src='content_image/772531.jpg'>

At this point, the query is optimized almost as well as possible. The remaining problem is that, by default, MySQL assumes that values in the tt.ActualPC  column are evenly distributed, and that is not the case for the tt  table. Fortunately, it is easy to tell MySQL to analyze the key distribution: 

mysql> ANALYZE TABLE tt; 

With the additional index information, the join is perfect and EXPLAIN  produces this result: 

<img src='content_image/772530.jpg'>

 The rows  column in the output from EXPLAIN  is an educated guess from the MySQL join optimizer. Check whether the numbers are even close to the truth by comparing the rows  product with the actual number of rows that the query returns. If the numbers are quite different, you might get better performance by using STRAIGHT_JOIN  in your SELECT  statement and trying to list the tables in a different order in the FROM  clause. (However, STRAIGHT_JOIN  may prevent indexes from being used because it disables semijoin transformations. See Section 8.2.2.1, “Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations” .) 

It is possible in some cases to execute statements that modify data when EXPLAIN SELECT  is used with a subquery; for more information, see Section 13.2.11.8, “Derived Tables” . 

## 8.8.3 Extended EXPLAIN Output Format 

The EXPLAIN  statement produces extra (“extended” ) information that is not part of EXPLAIN  output but can be viewed by issuing a SHOW WARNINGS  statement following EXPLAIN . As of MySQL 8.0.12, extended information is available for SELECT , DELETE , INSERT , REPLACE , and UPDATE  statements. Prior to 8.0.12, extended information is available only for SELECT  statements. 

