加速mysql查询 [英] Speeding up mysql query

问题描述

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

I have a mysql query to join four tables and I thought that it was just best to join tables but now that mysql data is getting bigger the query seems to cause the application to stop execution.

 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

我的问题确实是需要很长时间才能完成的查询.有没有办法加快此查询或更改此查询以更快地检索数据?

my problem really is the query that takes a lot of time to finish. Is there a way to speed this query or to change this query for faster retrieval of the data?

这里是评论中要求的 EXPLAIN EXTENED.提前致谢，我真的希望这是我提问的正确渠道.如果没有，请告诉我.

heres the EXPLAIN EXTENED as requested in the comments. Thanks in advance, I really hope this is the right channel for me to ask. If not please let me know.

推荐答案

这会为您提供正确的 id 列表吗?

Will this give you the correct list of ids?

SELECT id
    FROM purchase_order
    ORDER BY`po_date` DESC
    LIMIT 0, 20

如果是这样，那么在启动 JOIN 之前开始.您还可以(我认为)摆脱导致行爆炸-内爆"的 GROUP BY.

If so, then start with that before launching into the JOIN. You can also (I think) get rid of the GROUP BY that is causing an "explode-implode" of rows.

SELECT ...
    FROM ( SELECT id ... (as above) ...) AS ids
    JOIN purchase_order po  ON po.id = ids.id
    JOIN ... (the other tables)
    GROUP BY ...   -- (this may be problematic, especially with the LIMIT)
    ORDER BY po.po_date DESC   -- yes, this needs repeating
    -- no LIMIT

像这样

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 (SELECT id, po_date, po_number, customer_id, status
            FROM purchase_order
            ORDER BY `po_date` DESC
            LIMIT 0, 5) as 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 purchase_order.id DESC
    LIMIT 0, 5

这篇关于加速mysql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！