Assume the below schema, this is just created to test some features, and not a valid design for production. When I run the below query and check it's optimizer trace, I find lot of plans.
select
e.emp_no,
concat(e.first_name, ' ', e.last_name),
d.dept_name,
t.title_name,
es.salary,
es.insurance,
es.pf,
ea.city,
ea.state,
ea.phone
from employee e
join emp_salary es on e.emp_no = es.emp_no
join emp_title et on et.emp_no = e.emp_no
join title t on t.title_no = et.title_no
join emp_address_phone ea on ea.emp_no = e.emp_no
join emp_dept ed on e.emp_no = ed.emp_no
join department d on d.dept_no = ed.dept_no
where
(e.hire_date > '2004-01-01' or e.hire_date < '1980-01-01') and
(es.pf > 4.25 or es.pf < 1.4) and
(t.title_created > '2006-01-01') and
(ea.country = 'Spain' or ea.country = 'Samoa' or ea.country = 'India');
In the first plan that is..
d->t->et->ed->es->ea->e
..there is a condition filtering value when ed is joined to d->t->et that I am unable to make sense of.
I recreated this join order, with a straight join, I tried explain, and found the same.
+----+-------------+-------+------------+--------+-----------------+----------+---------+-------------------------------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+-----------------+----------+---------+-------------------------------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | d | NULL | ALL | PRIMARY | NULL | NULL | NULL | 100 | 100.00 | NULL |
| 1 | SIMPLE | t | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1000 | 33.33 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | et | NULL | ref | emp_no,title_no | title_no | 5 | employee_cond_filt.t.title_no | 5 | 100.00 | Using where |
| 1 | SIMPLE | ed | NULL | eq_ref | emp_no,dept_no | emp_no | 5 | employee_cond_filt.et.emp_no | 1 | **5.00** | Using where |
| 1 | SIMPLE | es | NULL | eq_ref | emp_no | emp_no | 5 | employee_cond_filt.et.emp_no | 1 | 55.55 | Using where |
| 1 | SIMPLE | ea | NULL | eq_ref | emp_no | emp_no | 5 | employee_cond_filt.et.emp_no | 1 | 27.10 | Using where |
| 1 | SIMPLE | e | NULL | eq_ref | PRIMARY | PRIMARY | 4 | employee_cond_filt.et.emp_no | 1 | 55.55 | Using where |
+----+-------------+-------+------------+--------+-----------------+----------+---------+-------------------------------+------+----------+----------------------------------------------------+
I understand
Filtering Factor (ea) = 0.1 + 0.1 + 0.1 - 0.01 - 0.01 - 0.01 = 0.27 (ea.country = 'Spain' or ea.country = 'Samoa' or ea.country = 'India' )
Filtering Factor (t) = 0.3333 (t.title_created > '2006-01-01')
Filtering Factor (es) = 0.3333 + 0.3333 - 0.1111 = 0.5555 (es.pf > 4.25 or es.pf < 1.4)
Filtering Factor (e) = 0.3333 + 0.3333 - 0.1111 = 0.5555 (e.hire_date > '2004-01-01' or e.hire_date < '1980-01-01')
But attached condition for ed is just (ed.dept_no = d.dept_no). Should the filter percent be 10 or the factor be 0.1 in that case?
