mysql - 加快mysql查询

Question

我有一个 mysql 查询来连接四个表，我认为最好是连接表，但现在 mysql 数据越来越大，查询似乎导致应用程序停止执行。

 SELECT 
                        `purchase_order`.`id`, 
                        `purchase_order`.`po_date` AS po_date, 
                        `purchase_order`.`po_number`, 
                        `purchase_order`.`customer_id` AS customer_id , 
                        `customer`.`name` AS customer_name, 
                        `purchase_order`.`status` AS po_status, 
                        `purchase_order_items`.`product_id`, 
                        `purchase_order_items`.`po_item_name`, 
                        `product`.`weight` as product_weight,
                        `product`.`pending` as product_pending,
                        `product`.`company_owner` as company_owner,
                        `purchase_order_items`.`uom`, 
                        `purchase_order_items`.`po_item_type`, 
                        `purchase_order_items`.`order_sequence`, 
                        `purchase_order_items`.`pending_balance`, 
                        `purchase_order_items`.`quantity`, 
                        `purchase_order_items`.`notes`, 
                        `purchase_order_items`.`status` AS po_item_status,
                        `purchase_order_items`.`id` AS po_item_id
                      FROM `purchase_order` 
                      INNER JOIN customer ON `customer`.`id` = `purchase_order`.`customer_id`  
                      INNER JOIN purchase_order_items ON `purchase_order_items`.`po_id` = `purchase_order`.`id` 
                      INNER JOIN product ON `purchase_order_items`.`product_id` = `product`.`id`
GROUP BY id ORDER BY `purchase_order`.`po_date` DESC LIMIT 0, 20

我的问题确实是需要花费大量时间才能完成的查询。有没有办法加快这个查询或改变这个查询以更快地检索数据？

这是评论中要求的EXPLAIN EXTENED。 在此先感谢，我真的希望这是我问的正确渠道。如果没有，请告诉我。

标签： mysqlperformance

Answer 1

您需要确保purchase_order.po_date所有 id 列都已编入索引。您可以使用以下查询进行检查。

SHOW INDEX FROM yourtable;