文章/答案/技术大牛

发布

社区首页 >问答首页 >MySQL左联接查询突然挂在一个db中，但在另一个db中运行。

问MySQL左联接查询突然挂在一个db中，但在另一个db中运行。
EN

Stack Overflow用户

提问于 2017-04-21 11:23:03

回答 2查看 141关注 0票数 0

我有一个SQL，它列出了与相关价格相关的文章，使用左联接来获得最新的价格。突然，当我尝试运行这个查询时，它开始挂起。如果我移除左联接，并执行常规连接，它将以正常的速度运行。但是我没有得到想要的结果

问题是它通常运行得很好。当我解释的时候，一切看起来都很好。我甚至有一个具有相同表和索引的旧数据库，如果在那里运行它，它将在1.5秒内完成。唯一的区别是数据更老了一点。

我有索引。

那么，我从哪里开始调试呢？我已经检查了损坏的表，并运行修复表只是为了确保。甚至重新启动服务器。还有什么要查的吗？

它运行在Ubuntu服务器14.04.5上，MySQL为5.6.22，我使用PHP连接到数据库。

SELECT a.art_id AS art_id, a.ecs_id AS ecs_id, a.parent_id AS parent_id, a.primarytext AS primarytext, a.serie AS serie, a.receipttext AS receipttext,
a.postername AS postername, a.posterdescription AS posterdescription, a. primarysupplier AS primarysupplier, a.ean1 AS ean1, a.supplierarticleno AS supplierarticleno,
a.dropdate AS dropdate, a.salesinfo AS salesinfo, a.packagesize,
sp.price AS utpris, sp.fromdate AS utprisFromdate, sp.todate AS utprisTodate, pp.price AS innpris, pp.fromdate AS innprisFromdate, pp.todate AS innprisTodate

FROM ecs_article AS a
LEFT JOIN ecs_purchaseprice pp ON a.art_id=pp.article_id
LEFT JOIN ecs_saleprice sp ON a.art_id=sp.article_id
WHERE a.primarysupplier='HK'
AND a.dropdate > now()
AND (a.art_id LIKE '%new connection%' OR a.primarytext LIKE '%new connection%' OR a.serie LIKE '%new connection%' OR a.parent_id LIKE '%new connection%')
AND ( sp.fromdate = (
SELECT max(fromdate)
from ecs_saleprice y
where y.article_id=sp.article_id
)
OR sp.fromdate IS NULL
)
AND ( pp.fromdate = (
SELECT max(fromdate)
from ecs_purchaseprice yp
where yp.article_id=pp.article_id
)
OR pp.fromdate IS NULL

)
GROUP BY a.art_id
ORDER BY a.art_id

这是解释的结果：

id  select_type        table   type  possible_keys       key            key_len     ref             rows Extra  
1   PRIMARY            a       range supDropDateIndex    supDropDateIndex   38      NULL            2257 Using index condition; Using where; Using temporary; Using filesort
1   PRIMARY            pp      ref   artIdFromdateIndex  artIdFromdateIndex 767     a.art_id        4    Using where
1   PRIMARY            sp      ref   spartididx          spartididx         26      a.art_id        5    Using where
3   DEPENDENT SUBQUERY yp      ref   artIdFromdateIndex  artIdFromdateIndex 767     pp.article_id   4    Using index
2   DEPENDENT SUBQUERY y       ref   spartididx          spartididx         26      sp.article_id   5    Using where

编辑:我已经修改了问题的措辞，并从查询中删除了不影响结果的内容。

mysql

sql

回答 2

Stack Overflow用户

回答已采纳

发布于 2017-04-21 12:59:40

SELECT语句中有超过30个子查询很可能会导致问题，特别是当返回的行数增加时。在WHERE子句中添加两个子查询，就会出现主要的潜在问题。

如果ecs_article表上有100 k行，那么很容易就会迫使SQL在内部执行超过300万次查询来返回结果集。随着返回的行而增加的一个庞大的数字。

您也有一个组BY，而不使用任何聚合函数。

如果有必要，我建议使用子查询。没有测试，但如下所示。这是为了处理ecs_artprops表中每个键的多个记录。

SELECT a.art_id AS art_id, 
        a.ecs_id AS ecs_id, 
        a.parent_id AS parent_id, 
        a.primarytext AS primarytext, 
        a.serie AS serie, 
        a.receipttext AS receipttext,
        a.postername AS postername, 
        a.posterdescription AS posterdescription, 
        a. primarysupplier AS primarysupplier, 
        a.ean1 AS ean1, 
        a.supplierarticleno AS supplierarticleno,
        a.dropdate AS dropdate, 
        a.salesinfo AS salesinfo, 
        a.packagesize,
        sp.price AS utpris, 
        sp.fromdate AS utprisFromdate, 
        sp.todate AS utprisTodate, 
        pp.price AS innpris, 
        pp.fromdate AS innprisFromdate, 
        pp.todate AS innprisTodate, 
        ECS_Height_sub.value AS ECS_Height, 
        ECS_Width_sub.value AS ECS_Width, 
        ECS_Length_sub.value AS ECS_Length, 
        ECS_Depth_sub.value AS ECS_Depth, 
        ECS_Depth2_sub.value AS ECS_Depth2, 
        ECS_Diameter_sub.value AS ECS_Diameter, 
        ECS_Space_sub.value AS ECS_Space, 
        ECS_Seatdepth_sub.value AS ECS_Seatdepth, 
        ECS_Seatheight_sub.value AS ECS_Seatheight, 
        ECS_Bedsize_sub.value AS ECS_Bedsize, 
        ECS_SurfaceTreat_sub.value AS ECS_SurfaceTreat, 
        ECS_Weight_sub.value AS ECS_Weight, 
        ECS_Volume_sub.value AS ECS_Volume, 
        ECS_Doors_sub.value AS ECS_Doors, 
        ECS_Shelfs_sub.value AS ECS_Shelfs, 
        ECS_Extensionleaves_sub.value AS ECS_Extensionleaves, 
        ECS_Dropleaves_sub.value AS ECS_Dropleaves, 
        ECS_Drawers_sub.value AS ECS_Drawers, 
        ECS_Other_sub.value AS ECS_Other, 
        ECS_Unit_sub.value AS ECS_Unit, 
        ECS_Brand_sub.value AS ECS_Brand, 
        ECS_Colour_sub.value AS ECS_Colour, 
        ECS_DeliveryTime_sub.value AS ECS_DeliveryTime, 
        ECS_Material_sub.value AS ECS_Material, 
        ECS_Mechanism_sub.value AS ECS_Mechanism, 
        ECS_AssemblyPrice_sub.value AS ECS_AssemblyPrice, 
        ECS_Assembled_sub.value AS ECS_Assembled, 
        ECS_Cushion_sub.value AS ECS_Cushion, 
        ECS_Manufacturer_sub.value AS ECS_Manufacturer, 
        ECS_FabricNeed_sub.value AS ECS_FabricNeed, 
        ECS_Size_sub.value AS ECS_Size
FROM ecs_article AS a
LEFT JOIN ecs_purchaseprice pp ON a.art_id=pp.article_id
LEFT OUTER JOIN ecs_saleprice sp ON a.art_id=sp.article_id
LEFT OUTER JOIN 
(
    SELECT article_id, max(fromdate) AS max_fromdate
    from ecs_saleprice
    GROUP BY article_id
) sub_max_fromdate_sale
ON sub_max_fromdate_sale.article_id = sp.article_id
LEFT OUTER JOIN 
(
    SELECT article_id, max(fromdate) AS max_fromdate
    from ecs_purchaseprice
    GROUP BY article_id
) sub_max_fromdate_purchase
ON sub_max_fromdate_purchase.article_id = sp.article_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Height' GROUP BY art_id) AS ECS_Height_sub  ON ECS_Height_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Width' GROUP BY art_id) AS ECS_Width_sub    ON ECS_Width_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Length' GROUP BY art_id) AS ECS_Length_sub  ON ECS_Length_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Depth' GROUP BY art_id) AS ECS_Depth_sub    ON ECS_Depth_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Depth2' GROUP BY art_id) AS ECS_Depth2_sub  ON ECS_Depth2_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Diameter' GROUP BY art_id) AS ECS_Diameter_sub  ON ECS_Diameter_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Space' GROUP BY art_id) AS ECS_Space_sub    ON ECS_Space_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Seatdepth' GROUP BY art_id) AS ECS_Seatdepth_sub    ON ECS_Seatdepth_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Seatheight' GROUP BY art_id) AS ECS_Seatheight_sub  ON ECS_Seatheight_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Bedsize' GROUP BY art_id) AS ECS_Bedsize_sub    ON ECS_Bedsize_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_SurfaceTreat' GROUP BY art_id) AS ECS_SurfaceTreat_sub  ON ECS_SurfaceTreat_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Weight' GROUP BY art_id) AS ECS_Weight_sub  ON ECS_Weight_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Volume' GROUP BY art_id) AS ECS_Volume_sub  ON ECS_Volume_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Doors' GROUP BY art_id) AS ECS_Doors_sub    ON ECS_Doors_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Shelfs' GROUP BY art_id) AS ECS_Shelfs_sub  ON ECS_Shelfs_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Extensionleaves' GROUP BY art_id) AS ECS_Extensionleaves_sub    ON ECS_Extensionleaves_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Dropleaves' GROUP BY art_id) AS ECS_Dropleaves_sub  ON ECS_Dropleaves_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Drawers' GROUP BY art_id) AS ECS_Drawers_sub    ON ECS_Drawers_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Other' GROUP BY art_id) AS ECS_Other_sub    ON ECS_Other_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Unit' GROUP BY art_id) AS ECS_Unit_sub  ON ECS_Unit_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Brand' GROUP BY art_id) AS ECS_Brand_sub    ON ECS_Brand_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Colour' GROUP BY art_id) AS ECS_Colour_sub  ON ECS_Colour_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_DeliveryTime' GROUP BY art_id) AS ECS_DeliveryTime_sub  ON ECS_DeliveryTime_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Material' GROUP BY art_id) AS ECS_Material_sub  ON ECS_Material_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Mechanism' GROUP BY art_id) AS ECS_Mechanism_sub    ON ECS_Mechanism_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_AssemblyPrice' GROUP BY art_id) AS ECS_AssemblyPrice_sub    ON ECS_AssemblyPrice_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Assembled' GROUP BY art_id) AS ECS_Assembled_sub    ON ECS_Assembled_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Cushion' GROUP BY art_id) AS ECS_Cushion_sub    ON ECS_Cushion_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Manufacturer' GROUP BY art_id) AS ECS_Manufacturer_sub  ON ECS_Manufacturer_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_FabricNeed' GROUP BY art_id) AS ECS_FabricNeed_sub  ON ECS_FabricNeed_sub.art_id = a.ecs_id
LEFT OUTER JOIN (SELECT art_id, MAX(value) FROM ecs_artprops WHERE prop_id='ECS_Size' GROUP BY art_id) AS ECS_Size_sub  ON ECS_Size_sub.art_id = a.ecs_id
WHERE a.primarysupplier='HK'
AND a.dropdate > now()
AND (a.art_id LIKE '%new connection%' OR a.primarytext LIKE '%new connection%' OR a.serie LIKE '%new connection%' OR a.parent_id LIKE '%new connection%')
AND ( sp.fromdate = sub_max_fromdate_sale.max_fromdate
OR sp.fromdate IS NULL)
AND ( pp.fromdate = sub_max_fromdate_purchase.max_fromdate
OR pp.fromdate IS NULL)
ORDER BY a.art_id

如果有多个记录，但数量不多，可能更容易使用：-

SELECT a.art_id AS art_id, 
        a.ecs_id AS ecs_id, 
        a.parent_id AS parent_id, 
        a.primarytext AS primarytext, 
        a.serie AS serie, 
        a.receipttext AS receipttext,
        a.postername AS postername, 
        a.posterdescription AS posterdescription, 
        a. primarysupplier AS primarysupplier, 
        a.ean1 AS ean1, 
        a.supplierarticleno AS supplierarticleno,
        a.dropdate AS dropdate, 
        a.salesinfo AS salesinfo, 
        a.packagesize,
        sp.price AS utpris, 
        sp.fromdate AS utprisFromdate, 
        sp.todate AS utprisTodate, 
        pp.price AS innpris, 
        pp.fromdate AS innprisFromdate, 
        pp.todate AS innprisTodate, 
        MAX(ECS_Height_sub.values) AS ECS_Height, 
        MAX(ECS_Width_sub.values) AS ECS_Width, 
        MAX(ECS_Length_sub.values) AS ECS_Length, 
        MAX(ECS_Depth_sub.values) AS ECS_Depth, 
        MAX(ECS_Depth2_sub.values) AS ECS_Depth2, 
        MAX(ECS_Diameter_sub.values) AS ECS_Diameter, 
        MAX(ECS_Space_sub.values) AS ECS_Space, 
        MAX(ECS_Seatdepth_sub.values) AS ECS_Seatdepth, 
        MAX(ECS_Seatheight_sub.values) AS ECS_Seatheight, 
        MAX(ECS_Bedsize_sub.values) AS ECS_Bedsize, 
        MAX(ECS_SurfaceTreat_sub.values) AS ECS_SurfaceTreat, 
        MAX(ECS_Weight_sub.values) AS ECS_Weight, 
        MAX(ECS_Volume_sub.values) AS ECS_Volume, 
        MAX(ECS_Doors_sub.values) AS ECS_Doors, 
        MAX(ECS_Shelfs_sub.values) AS ECS_Shelfs, 
        MAX(ECS_Extensionleaves_sub.values) AS ECS_Extensionleaves, 
        MAX(ECS_Dropleaves_sub.values) AS ECS_Dropleaves, 
        MAX(ECS_Drawers_sub.values) AS ECS_Drawers, 
        MAX(ECS_Other_sub.values) AS ECS_Other, 
        MAX(ECS_Unit_sub.values) AS ECS_Unit, 
        MAX(ECS_Brand_sub.values) AS ECS_Brand, 
        MAX(ECS_Colour_sub.values) AS ECS_Colour, 
        MAX(ECS_DeliveryTime_sub.values) AS ECS_DeliveryTime, 
        MAX(ECS_Material_sub.values) AS ECS_Material, 
        MAX(ECS_Mechanism_sub.values) AS ECS_Mechanism, 
        MAX(ECS_AssemblyPrice_sub.values) AS ECS_AssemblyPrice, 
        MAX(ECS_Assembled_sub.values) AS ECS_Assembled, 
        MAX(ECS_Cushion_sub.values) AS ECS_Cushion, 
        MAX(ECS_Manufacturer_sub.values) AS ECS_Manufacturer, 
        MAX(ECS_FabricNeed_sub.values) AS ECS_FabricNeed, 
        MAX(ECS_Size_sub.values) AS ECS_Size
FROM ecs_article AS a
LEFT JOIN ecs_purchaseprice pp ON a.art_id=pp.article_id
LEFT OUTER JOIN ecs_saleprice sp ON a.art_id=sp.article_id
LEFT OUTER JOIN 
(
    SELECT article_id, max(fromdate) AS max_fromdate
    from ecs_saleprice
    GROUP BY article_id
) sub_max_fromdate_sale
ON sub_max_fromdate_sale.article_id = sp.article_id
LEFT OUTER JOIN 
(
    SELECT article_id, max(fromdate) AS max_fromdate
    from ecs_purchaseprice
    GROUP BY article_id
) sub_max_fromdate_purchase
ON sub_max_fromdate_purchase.article_id = sp.article_id
LEFT OUTER JOIN ecs_artprops ECS_Height_sub ON ECS_Height_sub.art_id = a.ecs_id AND ECS_Height_sub.prop_id='ECS_Height'
LEFT OUTER JOIN ecs_artprops ECS_Width_sub ON ECS_Width_sub.art_id = a.ecs_id AND ECS_Width_sub.prop_id='ECS_Width'  
LEFT OUTER JOIN ecs_artprops ECS_Length_sub ON ECS_Length_sub.art_id = a.ecs_id AND ECS_Length_sub.prop_id='ECS_Length'  
LEFT OUTER JOIN ecs_artprops ECS_Depth_sub ON ECS_Depth_sub.art_id = a.ecs_id AND ECS_Depth_sub.prop_id='ECS_Depth'   
LEFT OUTER JOIN ecs_artprops ECS_Depth2_sub ON ECS_Depth2_sub.art_id = a.ecs_id AND ECS_Depth2_sub.prop_id='ECS_Depth2'  
LEFT OUTER JOIN ecs_artprops ECS_Diameter_sub ON ECS_Diameter_sub.art_id = a.ecs_id AND ECS_Diameter_sub.prop_id='ECS_Diameter'  
LEFT OUTER JOIN ecs_artprops ECS_Space_sub ON ECS_Space_sub.art_id = a.ecs_id AND ECS_Space_sub.prop_id='ECS_Space'  
LEFT OUTER JOIN ecs_artprops ECS_Seatdepth_sub ON ECS_Seatdepth_sub.art_id = a.ecs_id AND ECS_Seatdepth_sub.prop_id='ECS_Seatdepth'  
LEFT OUTER JOIN ecs_artprops ECS_Seatheight_sub ON ECS_Seatheight_sub.art_id = a.ecs_id AND ECS_Seatheight_sub.prop_id='ECS_Seatheight'  
LEFT OUTER JOIN ecs_artprops ECS_Bedsize_sub ON ECS_Bedsize_sub.art_id = a.ecs_id AND ECS_Bedsize_sub.prop_id='ECS_Bedsize'  
LEFT OUTER JOIN ecs_artprops ECS_SurfaceTreat_sub ON ECS_SurfaceTreat_sub.art_id = a.ecs_id AND ECS_SurfaceTreat_sub.prop_id='ECS_SurfaceTreat'  
LEFT OUTER JOIN ecs_artprops ECS_Weight_sub ON ECS_Weight_sub.art_id = a.ecs_id AND ECS_Weight_sub.prop_id='ECS_Weight'  
LEFT OUTER JOIN ecs_artprops ECS_Volume_sub ON ECS_Volume_sub.art_id = a.ecs_id AND ECS_Volume_sub.prop_id='ECS_Volume'  
LEFT OUTER JOIN ecs_artprops ECS_Doors_sub ON ECS_Doors_sub.art_id = a.ecs_id AND ECS_Doors_sub.prop_id='ECS_Doors'  
LEFT OUTER JOIN ecs_artprops ECS_Shelfs_sub ON ECS_Shelfs_sub.art_id = a.ecs_id AND ECS_Shelfs_sub.prop_id='ECS_Shelfs'  
LEFT OUTER JOIN ecs_artprops ECS_Extensionleaves_sub ON ECS_Extensionleaves_sub.art_id = a.ecs_id AND ECS_Extensionleaves_sub.prop_id='ECS_Extensionleaves'  
LEFT OUTER JOIN ecs_artprops ECS_Dropleaves_sub ON ECS_Dropleaves_sub.art_id = a.ecs_id AND ECS_Dropleaves_sub.prop_id='ECS_Dropleaves'  
LEFT OUTER JOIN ecs_artprops ECS_Drawers_sub ON ECS_Drawers_sub.art_id = a.ecs_id AND ECS_Drawers_sub.prop_id='ECS_Drawers'  
LEFT OUTER JOIN ecs_artprops ECS_Other_sub ON ECS_Other_sub.art_id = a.ecs_id AND ECS_Other_sub.prop_id='ECS_Other'   
LEFT OUTER JOIN ecs_artprops ECS_Unit_sub ON ECS_Unit_sub.art_id = a.ecs_id AND ECS_Unit_sub.prop_id='ECS_Unit'  
LEFT OUTER JOIN ecs_artprops ECS_Brand_sub ON ECS_Brand_sub.art_id = a.ecs_id AND ECS_Brand_sub.prop_id='ECS_Brand'  
LEFT OUTER JOIN ecs_artprops ECS_Colour_sub ON ECS_Colour_sub.art_id = a.ecs_id AND ECS_Colour_sub.prop_id='ECS_Colour'  
LEFT OUTER JOIN ecs_artprops ECS_DeliveryTime_sub ON ECS_DeliveryTime_sub.art_id = a.ecs_id AND ECS_DeliveryTime_sub.prop_id='ECS_DeliveryTime'  
LEFT OUTER JOIN ecs_artprops ECS_Material_sub ON ECS_Material_sub.art_id = a.ecs_id AND ECS_Material_sub.prop_id='ECS_Material'  
LEFT OUTER JOIN ecs_artprops ECS_Mechanism_sub ON ECS_Mechanism_sub.art_id = a.ecs_id AND ECS_Mechanism_sub.prop_id='ECS_Mechanism'  
LEFT OUTER JOIN ecs_artprops ECS_AssemblyPrice_sub ON ECS_AssemblyPrice_sub.art_id = a.ecs_id AND ECS_AssemblyPrice_sub.prop_id='ECS_AssemblyPrice'  
LEFT OUTER JOIN ecs_artprops ECS_Assembled_sub ON ECS_Assembled_sub.art_id = a.ecs_id AND ECS_Assembled_sub.prop_id='ECS_Assembled'  
LEFT OUTER JOIN ecs_artprops ECS_Cushion_sub ON ECS_Cushion_sub.art_id = a.ecs_id AND ECS_Cushion_sub.prop_id='ECS_Cushion'  
LEFT OUTER JOIN ecs_artprops ECS_Manufacturer_sub ON ECS_Manufacturer_sub.art_id = a.ecs_id AND ECS_Manufacturer_sub.prop_id='ECS_Manufacturer'  
LEFT OUTER JOIN ecs_artprops ECS_FabricNeed_sub ON ECS_FabricNeed_sub.art_id = a.ecs_id AND ECS_FabricNeed_sub.prop_id='ECS_FabricNeed'  
LEFT OUTER JOIN ecs_artprops ECS_Size_sub ON ECS_Size_sub.art_id = a.ecs_id AND ECS_Size_sub.prop_id='ECS_Size'  
WHERE a.primarysupplier='HK'
AND a.dropdate > now()
AND (a.art_id LIKE '%new connection%' OR a.primarytext LIKE '%new connection%' OR a.serie LIKE '%new connection%' OR a.parent_id LIKE '%new connection%')
AND ( sp.fromdate = sub_max_fromdate_sale.max_fromdate
OR sp.fromdate IS NULL)
AND ( pp.fromdate = sub_max_fromdate_purchase.max_fromdate
OR pp.fromdate IS NULL)
GROUP BY a.art_id,
        a.ecs_id,
        a.parent_id,
        a.primarytext,
        a.serie,
        a.receipttext,
        a.postername,
        a.posterdescription,
        a. primarysupplier,
        a.ean1,
        a.supplierarticleno,
        a.dropdate,
        a.salesinfo,
        a.packagesize,
        sp.price,
        sp.fromdate,
        sp.todate,
        pp.price,
        pp.fromdate,
        pp.todate
ORDER BY a.art_id

如果没有倍数，那么您很可能会丢弃组BY和聚合函数：

SELECT a.art_id AS art_id, 
        a.ecs_id AS ecs_id, 
        a.parent_id AS parent_id, 
        a.primarytext AS primarytext, 
        a.serie AS serie, 
        a.receipttext AS receipttext,
        a.postername AS postername, 
        a.posterdescription AS posterdescription, 
        a. primarysupplier AS primarysupplier, 
        a.ean1 AS ean1, 
        a.supplierarticleno AS supplierarticleno,
        a.dropdate AS dropdate, 
        a.salesinfo AS salesinfo, 
        a.packagesize,
        sp.price AS utpris, 
        sp.fromdate AS utprisFromdate, 
        sp.todate AS utprisTodate, 
        pp.price AS innpris, 
        pp.fromdate AS innprisFromdate, 
        pp.todate AS innprisTodate, 
        ECS_Height_sub.values AS ECS_Height, 
        ECS_Width_sub.values AS ECS_Width, 
        ECS_Length_sub.values AS ECS_Length, 
        ECS_Depth_sub.values AS ECS_Depth, 
        ECS_Depth2_sub.values AS ECS_Depth2, 
        ECS_Diameter_sub.values AS ECS_Diameter, 
        ECS_Space_sub.values AS ECS_Space, 
        ECS_Seatdepth_sub.values AS ECS_Seatdepth, 
        ECS_Seatheight_sub.values AS ECS_Seatheight, 
        ECS_Bedsize_sub.values AS ECS_Bedsize, 
        ECS_SurfaceTreat_sub.values AS ECS_SurfaceTreat, 
        ECS_Weight_sub.values AS ECS_Weight, 
        ECS_Volume_sub.values AS ECS_Volume, 
        ECS_Doors_sub.values AS ECS_Doors, 
        ECS_Shelfs_sub.values AS ECS_Shelfs, 
        ECS_Extensionleaves_sub.values AS ECS_Extensionleaves, 
        ECS_Dropleaves_sub.values AS ECS_Dropleaves, 
        ECS_Drawers_sub.values AS ECS_Drawers, 
        ECS_Other_sub.values AS ECS_Other, 
        ECS_Unit_sub.values AS ECS_Unit, 
        ECS_Brand_sub.values AS ECS_Brand, 
        ECS_Colour_sub.values AS ECS_Colour, 
        ECS_DeliveryTime_sub.values AS ECS_DeliveryTime, 
        ECS_Material_sub.values AS ECS_Material, 
        ECS_Mechanism_sub.values AS ECS_Mechanism, 
        ECS_AssemblyPrice_sub.values AS ECS_AssemblyPrice, 
        ECS_Assembled_sub.values AS ECS_Assembled, 
        ECS_Cushion_sub.values AS ECS_Cushion, 
        ECS_Manufacturer_sub.values AS ECS_Manufacturer, 
        ECS_FabricNeed_sub.values AS ECS_FabricNeed, 
        ECS_Size_sub.values AS ECS_Size
FROM ecs_article AS a
LEFT JOIN ecs_purchaseprice pp ON a.art_id=pp.article_id
LEFT OUTER JOIN ecs_saleprice sp ON a.art_id=sp.article_id
LEFT OUTER JOIN 
(
    SELECT article_id, max(fromdate) AS max_fromdate
    from ecs_saleprice
    GROUP BY article_id
) sub_max_fromdate_sale
ON sub_max_fromdate_sale.article_id = sp.article_id
LEFT OUTER JOIN 
(
    SELECT article_id, max(fromdate) AS max_fromdate
    from ecs_purchaseprice
    GROUP BY article_id
) sub_max_fromdate_purchase
ON sub_max_fromdate_purchase.article_id = sp.article_id
LEFT OUTER JOIN ecs_artprops ECS_Height_sub ON ECS_Height_sub.art_id = a.ecs_id AND ECS_Height_sub.prop_id='ECS_Height'
LEFT OUTER JOIN ecs_artprops ECS_Width_sub ON ECS_Width_sub.art_id = a.ecs_id AND ECS_Width_sub.prop_id='ECS_Width'  
LEFT OUTER JOIN ecs_artprops ECS_Length_sub ON ECS_Length_sub.art_id = a.ecs_id AND ECS_Length_sub.prop_id='ECS_Length'  
LEFT OUTER JOIN ecs_artprops ECS_Depth_sub ON ECS_Depth_sub.art_id = a.ecs_id AND ECS_Depth_sub.prop_id='ECS_Depth'   
LEFT OUTER JOIN ecs_artprops ECS_Depth2_sub ON ECS_Depth2_sub.art_id = a.ecs_id AND ECS_Depth2_sub.prop_id='ECS_Depth2'  
LEFT OUTER JOIN ecs_artprops ECS_Diameter_sub ON ECS_Diameter_sub.art_id = a.ecs_id AND ECS_Diameter_sub.prop_id='ECS_Diameter'  
LEFT OUTER JOIN ecs_artprops ECS_Space_sub ON ECS_Space_sub.art_id = a.ecs_id AND ECS_Space_sub.prop_id='ECS_Space'  
LEFT OUTER JOIN ecs_artprops ECS_Seatdepth_sub ON ECS_Seatdepth_sub.art_id = a.ecs_id AND ECS_Seatdepth_sub.prop_id='ECS_Seatdepth'  
LEFT OUTER JOIN ecs_artprops ECS_Seatheight_sub ON ECS_Seatheight_sub.art_id = a.ecs_id AND ECS_Seatheight_sub.prop_id='ECS_Seatheight'  
LEFT OUTER JOIN ecs_artprops ECS_Bedsize_sub ON ECS_Bedsize_sub.art_id = a.ecs_id AND ECS_Bedsize_sub.prop_id='ECS_Bedsize'  
LEFT OUTER JOIN ecs_artprops ECS_SurfaceTreat_sub ON ECS_SurfaceTreat_sub.art_id = a.ecs_id AND ECS_SurfaceTreat_sub.prop_id='ECS_SurfaceTreat'  
LEFT OUTER JOIN ecs_artprops ECS_Weight_sub ON ECS_Weight_sub.art_id = a.ecs_id AND ECS_Weight_sub.prop_id='ECS_Weight'  
LEFT OUTER JOIN ecs_artprops ECS_Volume_sub ON ECS_Volume_sub.art_id = a.ecs_id AND ECS_Volume_sub.prop_id='ECS_Volume'  
LEFT OUTER JOIN ecs_artprops ECS_Doors_sub ON ECS_Doors_sub.art_id = a.ecs_id AND ECS_Doors_sub.prop_id='ECS_Doors'  
LEFT OUTER JOIN ecs_artprops ECS_Shelfs_sub ON ECS_Shelfs_sub.art_id = a.ecs_id AND ECS_Shelfs_sub.prop_id='ECS_Shelfs'  
LEFT OUTER JOIN ecs_artprops ECS_Extensionleaves_sub ON ECS_Extensionleaves_sub.art_id = a.ecs_id AND ECS_Extensionleaves_sub.prop_id='ECS_Extensionleaves'  
LEFT OUTER JOIN ecs_artprops ECS_Dropleaves_sub ON ECS_Dropleaves_sub.art_id = a.ecs_id AND ECS_Dropleaves_sub.prop_id='ECS_Dropleaves'  
LEFT OUTER JOIN ecs_artprops ECS_Drawers_sub ON ECS_Drawers_sub.art_id = a.ecs_id AND ECS_Drawers_sub.prop_id='ECS_Drawers'  
LEFT OUTER JOIN ecs_artprops ECS_Other_sub ON ECS_Other_sub.art_id = a.ecs_id AND ECS_Other_sub.prop_id='ECS_Other'   
LEFT OUTER JOIN ecs_artprops ECS_Unit_sub ON ECS_Unit_sub.art_id = a.ecs_id AND ECS_Unit_sub.prop_id='ECS_Unit'  
LEFT OUTER JOIN ecs_artprops ECS_Brand_sub ON ECS_Brand_sub.art_id = a.ecs_id AND ECS_Brand_sub.prop_id='ECS_Brand'  
LEFT OUTER JOIN ecs_artprops ECS_Colour_sub ON ECS_Colour_sub.art_id = a.ecs_id AND ECS_Colour_sub.prop_id='ECS_Colour'  
LEFT OUTER JOIN ecs_artprops ECS_DeliveryTime_sub ON ECS_DeliveryTime_sub.art_id = a.ecs_id AND ECS_DeliveryTime_sub.prop_id='ECS_DeliveryTime'  
LEFT OUTER JOIN ecs_artprops ECS_Material_sub ON ECS_Material_sub.art_id = a.ecs_id AND ECS_Material_sub.prop_id='ECS_Material'  
LEFT OUTER JOIN ecs_artprops ECS_Mechanism_sub ON ECS_Mechanism_sub.art_id = a.ecs_id AND ECS_Mechanism_sub.prop_id='ECS_Mechanism'  
LEFT OUTER JOIN ecs_artprops ECS_AssemblyPrice_sub ON ECS_AssemblyPrice_sub.art_id = a.ecs_id AND ECS_AssemblyPrice_sub.prop_id='ECS_AssemblyPrice'  
LEFT OUTER JOIN ecs_artprops ECS_Assembled_sub ON ECS_Assembled_sub.art_id = a.ecs_id AND ECS_Assembled_sub.prop_id='ECS_Assembled'  
LEFT OUTER JOIN ecs_artprops ECS_Cushion_sub ON ECS_Cushion_sub.art_id = a.ecs_id AND ECS_Cushion_sub.prop_id='ECS_Cushion'  
LEFT OUTER JOIN ecs_artprops ECS_Manufacturer_sub ON ECS_Manufacturer_sub.art_id = a.ecs_id AND ECS_Manufacturer_sub.prop_id='ECS_Manufacturer'  
LEFT OUTER JOIN ecs_artprops ECS_FabricNeed_sub ON ECS_FabricNeed_sub.art_id = a.ecs_id AND ECS_FabricNeed_sub.prop_id='ECS_FabricNeed'  
LEFT OUTER JOIN ecs_artprops ECS_Size_sub ON ECS_Size_sub.art_id = a.ecs_id AND ECS_Size_sub.prop_id='ECS_Size'  
WHERE a.primarysupplier='HK'
AND a.dropdate > now()
AND (a.art_id LIKE '%new connection%' OR a.primarytext LIKE '%new connection%' OR a.serie LIKE '%new connection%' OR a.parent_id LIKE '%new connection%')
AND ( sp.fromdate = sub_max_fromdate_sale.max_fromdate
OR sp.fromdate IS NULL)
AND ( pp.fromdate = sub_max_fromdate_purchase.max_fromdate
OR pp.fromdate IS NULL)
ORDER BY a.art_id

票数 3

Stack Overflow用户

发布于 2017-04-24 09:15:50

我将存储引擎更改为InnoDB，删除索引并再次创建它们。现在一切都恢复正常了。去想想..。

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/43541477

复制

相似问题

问MySQL左联接查询突然挂在一个db中，但在另一个db中运行。
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL左联接查询突然挂在一个db中，但在另一个db中运行。EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL左联接查询突然挂在一个db中，但在另一个db中运行。
EN