代码之家 › 专栏 › 技术社区 › Ajay Jirati

按描述查询订单优化

sql-order-by mysql

Ajay Jirati · 技术社区 · 7 年前

表“taking”中的记录总数=12000。

我有下面的查询,其中只需要10条记录,这是最近或最后添加的记录。所以我应用了OrderBy desc,但当我使用ORDER BY on bid应用desc时,它将挂起,查询将在15分钟内返回10条记录的输出。

 SELECT taking.BId ,taking.calledDate Called,taking.calleduser Caller,taking.callerNotes,taking.Title,
taking.FName,taking.ScName,taking.takingPhase, Training.TName,
taking.TrDate,taking.SubmitDateTime, TrLocation.TrLocation trTrl,
taking.STATUS,taking.DelegNum, taking.Tel, taking.Email,taking.Notes
FROM Taking taking
LEFT JOIN training Training ON taking.Training = Training.TId
LEFT JOIN confirmation ON taking.Bid = confirmation.Bid
LEFT JOIN TrLocation ON taking.TrLocation = TrLocation.TrLId
LEFT JOIN Invoice ON taking.BId =Invoice.BId
LEFT JOIN couriertracking ON taking.BId = couriertracking.bId
LEFT JOIN SalesPerson ON taking.SId = SalesPerson.SId WHERE
taking.SubmitDateTime > DATE_SUB(NOW(), INTERVAL 2 MONTH)  AND IFNULL(taking.SId,0) > 0
ORDER BY taking.BId DESC LIMIT 10

我尝试在bid列建立索引,但查询时间仍然相同。如何优化查询?

1 回复 | 直到 7 年前

Paul Spiegel 7 年前

由于只有左连接,并且左连接只能添加更多行,因此可以限制从 Taking 具有子查询的表。然后,只需要将10行与其他表连接起来。如果任何联接都将添加更多行,则仍可以在外部查询中使用另一个限制。

SELECT
    taking.BId,taking.calledDate Called, taking.calleduser Caller,
    taking.callerNotes, taking.Title, taking.FName, taking.ScName,
    taking.takingPhase, Training.TName, taking.TrDate,
    taking.SubmitDateTime, TrLocation.TrLocation trTrl, taking.STATUS,
    taking.DelegNum, taking.Tel, taking.Email,taking.Notes
FROM (
    SELECT taking.*
    FROM Taking taking
    WHERE taking.SubmitDateTime > DATE_SUB(NOW(), INTERVAL 2 MONTH)
      AND taking.SId > 0
    ORDER BY taking.BId DESC
    LIMIT 10
) taking
LEFT JOIN training Training ON taking.Training = Training.TId
LEFT JOIN confirmation ON taking.Bid = confirmation.Bid
LEFT JOIN TrLocation ON taking.TrLocation = TrLocation.TrLId
LEFT JOIN Invoice ON taking.BId =Invoice.BId
LEFT JOIN couriertracking ON taking.BId = couriertracking.bId
LEFT JOIN SalesPerson ON taking.SId = SalesPerson.SId
ORDER BY taking.BId DESC
LIMIT 10

请注意 taking.SId > 0 具有与相同的效果 IFNULL(taking.SId,0) > 0 在WHERE子句中。具有的行 NULL 在里面 SId 将不符合该条件。

但是,您仍然应该验证在每个表中用于连接的每一列上都有索引。对于此查询,您需要索引

Training.TId
confirmation.Bid
TrLocation.TrLId
Invoice.BId
couriertracking.bId
SalesPerson.SId

其中一些看起来像主键。如果是这样的话,就不需要为它们编制索引。

为 拿 表格并不是那么简单。但12K排并没有那么多。只要打开索引就可以了 SubmitDateTime .