请教一下,怎么样才能是下面这个查询效率高一点.
我了将排序省掉,速度就快很多,还有一个where条件,省掉也快很多! 但是一起就很慢.
SQL如下:
SELECT
shipping.*,
FORMAT(shipTotalWeight, 2) AS totalWeight,
contanum,
contanumbeer
FROM shipping
LEFT JOIN
(SELECT transhipid,CONCAT(contanumbeer,';') AS contanumbeer,COUNT(*) AS contanum FROM transport GROUP BY transhipid) AS transport1
ON transport1.transhipid=shipping.shipid
where 1=1
ORDER BY shipping.shipid DESC
----------------------------------------------------------------------------------------
文本sql:
SELECT
shipping.*,
FORMAT(shipTotalWeight, 2) AS totalWeight,
contanum,
contanumbeer
FROM shipping
LEFT JOIN
(SELECT transhipid,CONCAT(contanumbeer,';') AS contanumbeer,COUNT(*) AS contanum FROM transport GROUP BY transhipid) AS transport1
ON transport1.transhipid=shipping.shipid
ORDER BY shipping.shipid DESC
----------------------------------------------------------------------------------------
EXPLAIN截图:
我了将排序省掉,速度就快很多,还有一个where条件,省掉也快很多! 但是一起就很慢.
SQL如下:
SELECT
shipping.*,
FORMAT(shipTotalWeight, 2) AS totalWeight,
contanum,
contanumbeer
FROM shipping
LEFT JOIN
(SELECT transhipid,CONCAT(contanumbeer,';') AS contanumbeer,COUNT(*) AS contanum FROM transport GROUP BY transhipid) AS transport1
ON transport1.transhipid=shipping.shipid
where 1=1
ORDER BY shipping.shipid DESC
----------------------------------------------------------------------------------------
文本sql:
SELECT
shipping.*,
FORMAT(shipTotalWeight, 2) AS totalWeight,
contanum,
contanumbeer
FROM shipping
LEFT JOIN
(SELECT transhipid,CONCAT(contanumbeer,';') AS contanumbeer,COUNT(*) AS contanum FROM transport GROUP BY transhipid) AS transport1
ON transport1.transhipid=shipping.shipid
ORDER BY shipping.shipid DESC
----------------------------------------------------------------------------------------
EXPLAIN截图:
shipping.*,
FORMAT(shipTotalWeight, 2) AS totalWeight,
COUNT(*) AS contanum,
CONCAT(contanumbeer,';') AS contanumbeer
FROM shipping
LEFT JOIN transport ON shipping.shipid = transport.transhipid
GROUP BY transport.transhipid
ORDER BY shipping.shipid DESC
我改成上面这样,这样会导致一个问题.
shipping 表有数据,transport.transhipid为null 时, 数据查不出来,该怎么改啊 求教大婶们
IFNULL(transhipid ,1)
你这么写, 是先连接然后再排序, 当然会丢失数据了.
不是transport.transhipid为空无数据, 而是所有 transport.transhipid 的空值,被分成了一个组.
你查询里面多加入一个transport 表字段, 就明白了.
至于说优化, 表创建联合索引试试.你条件里面带了 1=1 ? 这样写, 我有看到过, 说这么写,会导致查询变慢. 我也不知道原因.