mysql 卡住了。这条sql语句以前能用的,但增了8000条数据就卡了

kstsca 发布于 2012/06/14 20:38
阅读 1K+
收藏 0
SELECT COUNT(*) FROM `dbkxdhk`.`shop_goods` AS g ,(SELECT goods_id, attr_value AS cut FROM `dbkxdhk`.`shop_goods_attr` WHERE attr_id=2) AS cut,(SELECT goods_id, attr_value AS color FROM `dbkxdhk`.`shop_goods_attr` WHERE attr_id=3) AS color,(SELECT goods_id, attr_value AS symmetry FROM `dbkxdhk`.`shop_goods_attr` WHERE attr_id=6) AS symmetry,(SELECT goods_id, attr_value AS clarity FROM `dbkxdhk`.`shop_goods_attr` WHERE attr_id=4) AS clarity,(SELECT goods_id, attr_value AS polish FROM `dbkxdhk`.`shop_goods_attr` WHERE attr_id=5) AS polish,(SELECT goods_id, attr_value AS cert FROM `dbkxdhk`.`shop_goods_attr` WHERE attr_id=8) AS cert,(SELECT goods_id, attr_value AS carat FROM `dbkxdhk`.`shop_goods_attr` WHERE attr_id=1) AS carat,(SELECT goods_id, attr_value AS location FROM `dbkxdhk`.`shop_goods_attr` WHERE attr_id=62) AS location,(SELECT goods_id, attr_value AS certificate FROM `dbkxdhk`.`shop_goods_attr` WHERE attr_id=8) AS certificate WHERE g.goods_id = cut.goods_id AND g.goods_id = color.goods_id AND g.goods_id = symmetry.goods_id AND g.goods_id = clarity.goods_id AND g.goods_id = polish.goods_id AND g.goods_id = cert.goods_id AND g.goods_id = carat.goods_id AND g.goods_id = location.goods_id AND g.goods_id = certificate.goods_id AND g.is_on_sale = 1 AND g.cat_id=1 AND g.is_alone_sale = 1 AND g.is_delete = 0 AND g.cat_id IN ('1','2','7','9','8')
有什么办法么?如何简单实现不卡么。。。虽然这sql能撮,呵呵。。但不至于会卡吧
加载中
0
OrionBox
OrionBox
有人研究过。说是少用in。。
0
飞晏
飞晏

您这个是9个表在联查么?还用IN的...

有些逻辑你完全可以放在程序中去处理的。

0
盘先海
盘先海
我上次也是in300多个目录,30W数据直接空白,之前都正常。
0
Teo
Teo
能看懂它都需要花上些时间,修改的话就惨了。。。。。
0
魔力猫
魔力猫
看执行计划。到底哪里消耗资源最大就优化哪里。
0
UlricQin
UlricQin
汗,这个sql太吓人了
0
C
Corz
不做好适量的优化后果就是这样了
0
匿名t3a
匿名t3a

狂晕

    AND g.goods_id = color.goods_id

    AND g.goods_id = symmetry.goods_id

    AND g.goods_id = clarity.goods_id

    AND g.goods_id = polish.goods_id

    AND g.goods_id = cert.goods_id

    AND g.goods_id = carat.goods_id

    AND g.goods_id = location.goods_id

    AND g.goods_id = certificate.goods_id



shop_goods_attr 表联询9次
为什么要这样写?


SELECT COUNT(*)
FROM `dbkxdhk`.`shop_goods` AS g, `dbkxdhk`.`shop_goods_attr` AS ga
WHERE g.goods_id = ga.goods_id
    AND g.is_on_sale = 1
    AND g.cat_id = 1
    AND g.is_alone_sale = 1
    AND g.is_delete = 0
    AND g.cat_id IN('1','2','7','9','8')
    AND ga.attr_id IN(2,3,6,4,5,8,1,62)

帮简化了下看看能不能查询
还是很慢的话 要检查下索引有没有做好

返回顶部
顶部