Multiple left join optimizations

< H2 > tables: < / H2 >

employee
employee_orgn: federated primary key (employee_id,orgn_id), index 1, 2:orgn_id employees, index
orgn

< H2 > sql: < / H2 >

explain SELECT DISTINCT
    e.*
FROM
    employee e
        LEFT JOIN
    employee_orgn eo ON eo.employee_id = e.id
        LEFT JOIN
    orgn o ON o.id = eo.orgn_id
WHERE
    e.state != "deleted"
        AND e.state != "hidden"
        AND (o.state != "hidden" OR o.state IS NULL)
ORDER BY e.id DESC

< H2 > explain: < / H2 >

here join joint search, mysql nested loop query to the order of magnitude of 10 to the 8th?
Why is there a temporary table, and why the sorting is file sorting
Why is the second row an override index
would like to explain the explain result and optimize the analysis

Thank you very much ~

Java oracle sql mysql

Mar.31,2021

1. Notice that the header key_len, of the number 8 refers to the length of the index field.
2. Using tempory, Using filesort is affected by the order by statement. You can remove the order by and take a look at the execution plan.
3. It is reasonable for eo tables to use an overlay index because the index already contains all the data needed for the query.

Previous: How to configure subulime text3 python code completion?

Next: Use v-else after v-for in VUE uses v-if at the same time