CODEWITHSUNDEEP
mysqlsql
Chuckie Li
Chuckie Li

Reputation: 45

Multiple left join optimization

tables:

Some employee have no organization.

sql:

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

explain:

| id   | select_type    |  table | type | possible_keys | key  | key_len | ref  | rows | Extra |

| 1        | SIMPLE     |   e    |all        | NULL     |   NULL    |NULL        | NULL      |   12792    |Using where;USing tempory;Using filesort       | 

| 1        | SIMPLE     |   eo    |index        | PRIMARY     |   idx_orgn_id    |8        | NULL     |   13226  |Using index:Distinct  | 

| 1        | SIMPLE     |   o    |eq_ref        | PRIMARY      |   PRIMARY    |8        | eo.orgn_id      |   1    |Using where:Distinct           |

Q：

  1. Here left join, mysql nested loop query 10 orders of magnitude 8?
  2. Why are there temporary tables, and why sorting is file sorting?
  3. Why the second line is the overlay index
  4. I hope someone will explain this explain result and optimize the analysis.

Thanks in advance.

Upvotes: 0

Views: 1605

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

I would recommend re-writing the query -- to get rid of the select distinct. I think this is the logic you want:

SELECT e.*
FROM employee e
WHERE e.state not in ('deleted', 'hidden')
      NOT EXISTS (SELECT 1
                  FROM employee_orgn eo JOIN
                       orgn o
                       ON o.id = eo.orgn_id AND o.state = 'hidden'
                  WHERE eo.employee_id = e.id
                 )
ORDER BY e.id DESC;

For this query, you want an index on employee_orgn(employee_id, orgn_id) and orgn(id, state).

Upvotes: 0

Barmar
Barmar

Reputation: 780949

You shouldn't put conditions on tables that are left joined in the WHERE clause. Instead, put them in the ON clause. Then you don't need to use OR o.state IS NULL, which causes optimizer problems.

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 AND o.state != hidden
WHERE
    e.state NOT IN ('deleted', 'hidden')
ORDER BY e.id DESC

Upvotes: 1

Related Questions