SELECT
t.str_code AS strCode,
t.str_unit AS strUnitName,
COUNT( m.uuid ) AS intNum
FROM
( SELECT str_code, str_unit, ifnull( INT_ORDER, 0 ) AS intOrder FROM base_unit WHERE int_unit_level = 1 ) t
LEFT JOIN members m ON m.unit_fenju_uid = t.str_code
AND m.STR_MEMBER_TYPE = 'normal'
LEFT JOIN GROUP_LINK l ON l.STR_MEMBER_UID = m.uuid
WHERE
1 = 1
GROUP BY
t.str_code,
t.str_unit,
t.intOrder
ORDER BY
COUNT( m.uuid ) DESC,
t.intOrder ASC
orcl向mysql中移植发现查询好慢 mysql要120s orcl秒出
t.str_code AS strCode,
t.str_unit AS strUnitName,
COUNT( m.uuid ) AS intNum
FROM
( SELECT str_code, str_unit, ifnull( INT_ORDER, 0 ) AS intOrder FROM base_unit WHERE int_unit_level = 1 ) t
LEFT JOIN members m ON m.unit_fenju_uid = t.str_code
AND m.STR_MEMBER_TYPE = 'normal'
LEFT JOIN GROUP_LINK l ON l.STR_MEMBER_UID = m.uuid
WHERE
1 = 1
GROUP BY
t.str_code,
t.str_unit,
t.intOrder
ORDER BY
COUNT( m.uuid ) DESC,
t.intOrder ASC
orcl向mysql中移植发现查询好慢 mysql要120s orcl秒出
2. 在 members.unit_fenju_uid 上加个索引试试;看看效果,再继续
2.子查询t没有必要,把里面where条件放到外层最后也一样;
3.不清楚你的表结构,不知道你的group by 是什么用途
另外看你的几个表,都没有索引,怎么加索引,百度一下吧,好多内容的。
-- SELECT
-- t.str_code AS strcode,
-- t.str_unit AS strunitname,
-- COUNT(m.uuid) AS intnum
-- FROM
-- base_unit t
-- LEFT JOIN members m ON
-- m.unit_fenju_uid = t.str_code
-- AND
-- m.str_member_type = 'normal'
-- LEFT JOIN group_link l ON l.str_member_uid = m.uuid
-- WHERE
-- t.int_unit_level = 1
-- GROUP BY
-- t.str_code,
-- t.str_unit,
-- ifnull(t.int_order,0)
-- ORDER BY
-- COUNT(m.uuid) DESC,
-- ifnull(t.int_order,0) ASC ;