MySQL - 如何确定要索引哪些列？

Question

如何确定要索引哪些列？

SELECT a.ORD_ID AS Manual_Added_Orders,
   a.ORD_poOrdID_List AS Auto_Added_Orders,
   a.ORDPOITEM_ModelNumber,
   a.ORDPO_Number,
   a.ORDPOITEM_ID,
   (SELECT sum(ORDPOITEM_Qty) AS ORDPOITEM_Qty
      FROM orderpoitems
     WHERE     ORDPOITEM_ModelNumber = a.ORDPOITEM_ModelNumber
           AND ORDPO_Number = 123007)
      AS ORDPOITEM_Qty,
   a.ORDPO_TrackingNumber,
   a.ORDPOITEM_Received,
   a.ORDPOITEM_ReceivedQty,
   a.ORDPOITEM_ReceivedBy,
   b.ORDPO_ID
FROM orderpoitems a
   LEFT JOIN orderpo b ON (a.ORDPO_Number = b.ORDPO_Number)
WHERE a.ORDPO_Number = 123007
GROUP BY a.ORDPOITEM_ModelNumber
ORDER BY a.ORD_poOrdID_List, a.ORD_ID

我做了解释，这就是我如何获取这些图片...我添加了一些索引...看起来仍然不太好。

Answer 1

首先，您的查询可以简化为:

SELECT a.ORD_ID AS Manual_Added_Orders,
 a.ORD_poOrdID_List AS Auto_Added_Orders,
 a.ORDPOITEM_ModelNumber,
 a.ORDPO_Number,
 a.ORDPOITEM_ID,
 SUM(ORDPOITEM_Qty) AS ORDPOITEM_Qty
 a.ORDPO_TrackingNumber,
 a.ORDPOITEM_Received,
 a.ORDPOITEM_ReceivedQty,
 a.ORDPOITEM_ReceivedBy,
 b.ORDPO_ID
FROM orderpoitems a
LEFT JOIN orderpo b ON (a.ORDPO_Number = b.ORDPO_Number)
WHERE a.ORDPO_Number = 123007
GROUP BY a.ORDPOITEM_ModelNumber
ORDER BY a.ORD_poOrdID_List, a.ORD_ID

其次，我将首先在 orderpoitems.ORDPO_Number 和 orderpo.ORDPO_number 上创建索引

如果没有表结构，就很难说。