Table 2: organizational structure table, the table name hr_department_parent, data volume 6000 +
Requirements:=2 and table, the query cost business company (creator ID=2487 or departments is equal to 884 and the data from the subordinate departments)
Plan 1
884 and subordinate departments into constant
Select a. * from cost_expense_account a
Where a.c reatorId=2487 or a. d. epartmentId in panyId=2 (445446447884) and a.com
- order by Anderson d desc
Limit 10000;
Performance is good, can use creatorId and departmentId index
But the extreme situation, can have 3000 + spell out this string, just slow
Scheme 2
Select a. * from cost_expense_account a
The where (a.c reatorId=2487 or a. d. epartmentId in (select departmentId from hr_department_parent where parentId=884)) and companyId=2
Can't use departmentId index, slow
Plan 3, do not use IN, to JOIN
Select a. * from cost_expense_account a
Left the JOIN hr_department_parent on b (a. d. epartmentId=b.d epartmentId and a. d. epVersion=b.d epVersion) and a.c reatorId<> 2487
The where (a.c reatorId=2487 or p. arentId=884) and a.com panyid=2
The order by id desc limit 10000;
;
Effect: if there is no and a.com panyid=2, 0.2 S speed is good, add the and a.com panyid=2, need 4 seconds
4, with the UNION
The SELECT m. *
The FROM ` cost_expense_account ` m
The JOIN
(
SELECT id FROM ` cost_expense_account ` a1 WHERE a1. ` creatorId `=2487
UNION ALL
The SELECT Anderson d
The FROM ` cost_expense_account ` a
The JOIN ` hr_department_parent ` ON b (a. ` departmentId `=b. ` departmentId ` AND a. ` depVersion `=b. ` depVersion ` AND b. ` parentId `=884)
) n ON m.i d=n.i d
The order by desc m.i d limit 10000;
Effect: 8 seconds
Consult everybody warrior has any plan
