
获得徽章 0
赞了这篇文章
select u.user_id, u.dept_id, u.nick_name, u.user_name, u.email, u.avatar, u.phonenumber, u.sex, u.status, u.view_type,
u.del_flag, u.login_ip, u.login_date, u.create_by, u.create_time, u.remark,u.in_date,u.work_date,u.birth_date,u.company_post,
u.invite_code,u.open_id, COALESCE(d.name, d1.name) as name
from sys_user u
left join A d on u.dept_id= d.id
left join B d1 on u.dept_id = d1.id
where u.del_flag = '0'
AND (CASE WHEN LEFT(DEPT_ID, 1) != '1'
THEN (u.dept_id IN ( SELECT t.id FROM A t WHERE t.id = '931'
or find_in_set('931', t.ancestors) ))
ELSE (u.dept_id IN ( SELECT t.id FROM B t WHERE t.id = '1931'
or find_in_set('1931', t.ancestors) ))
END
);有没有大佬优化这个SQL,唯一索引和一般索引都加了,每张表里面都有四五千的数据。现在跑一次10秒,有可能还要加条件时间更长
u.del_flag, u.login_ip, u.login_date, u.create_by, u.create_time, u.remark,u.in_date,u.work_date,u.birth_date,u.company_post,
u.invite_code,u.open_id, COALESCE(d.name, d1.name) as name
from sys_user u
left join A d on u.dept_id= d.id
left join B d1 on u.dept_id = d1.id
where u.del_flag = '0'
AND (CASE WHEN LEFT(DEPT_ID, 1) != '1'
THEN (u.dept_id IN ( SELECT t.id FROM A t WHERE t.id = '931'
or find_in_set('931', t.ancestors) ))
ELSE (u.dept_id IN ( SELECT t.id FROM B t WHERE t.id = '1931'
or find_in_set('1931', t.ancestors) ))
END
);有没有大佬优化这个SQL,唯一索引和一般索引都加了,每张表里面都有四五千的数据。现在跑一次10秒,有可能还要加条件时间更长
![[流泪]](http://lf-web-assets.juejin.cn/obj/juejin-web/xitu_juejin_web/img/jj_emoji_6.dde0d83.png)
展开
14
点赞