MySQL - 如何确定要索引的列？

How do I figure out which columns to index?

如何确定要索引的列？

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

I did the explain that is how I am getting these pictures... I added a few indexes... still not looking good.

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

2 个解决方案

#1

Well firstly your query could be simplified to:

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

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

Secondly I would start by creating a index on the orderpoitems.ORDPO_Number and orderpo.ORDPO_number

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

Bit hard to say without the table structures.

没有桌子结构就很难说。

#2

Read up on indexes and covering index

阅读索引和覆盖索引

From what you have, start with what is in your where clause AND join criteria to another table. Also, include if possible and practical, those columns used in group by / order by as order by is typically a killer when finishing a query.

根据您的内容，从where子句中的内容开始，将条件连接到另一个表。此外，如果可能且实用，则包括按顺序使用/按顺序排序的那些列在完成查询时通常是杀手。

That said, I would have an index on your OrderPOItems table on

也就是说，我的OrderPOItems表上会有一个索引

( ordpo_number, orderpoitem_ModelNumber, ord_poordid_list, ord_id )

（ordpo_number，orderpoitem_ModelNumber，ord_poordid_list，ord_id）

This way, the FIRST element hits your WHERE clause. Next the column for your data grouping, finally, the columns for your order by. This way, the joins and qualifying components can be "covered" from the index alone without having to go to the raw data pages for the rest of the columns being returned. Hopefully a good jump start specific to your scenario.

这样，FIRST元素就会遇到你的WHERE子句。接下来是数据分组的列，最后是您的订单列。这样，连接和限定组件可以单独从索引“覆盖”，而不必转到返回的其余列的原始数据页。希望你的场景特别适合跳跃。