今天听到客户反馈,数据查询特别慢,检查之后发现了个SB操作。
SQL代码如下:
SELECT
*
FROM
(
SELECT
t.id,
t.tname,
t.tpic,
t.cid,
t.bcount,
REPLACE (t.product, ',', '|') product,
REPLACE (t.reg_group, ',', '|') reg_group,
t.reg_code,
t.tremark,
t.scount_str,
(
CASE
WHEN t.price_hide = 1 THEN
NULL
ELSE
r.price
END
) price,
c. NAME cname,
t. STATUS,
t.price_hide,
t.create_time,
t.id AS ronum
FROM
trademark t
left JOIN trademark_category c ON t.cid = c.cgcount
left JOIN trademark_user_relation r ON t.id = r.tid
WHERE
t. ENABLE = 1
AND t. STATUS = 1
) a
WHERE
(price_hide = 1)
ORDER BY
bcount DESC,
create_time DESC,
ronum ASC,
tname ASC
LIMIT 15
乍一看 没看出来问题在哪,这么简单的查询怎么会20多秒…
后来想明白了,这个查询用了子查询,子查询是先把所有的数据全查了出来…然后在外层判断,并且limit 15