Mysql查询需要大约10分钟才能执行

问题描述：

I have the following mysql query to fetch total amount and tax amount for each bill.

SELECT b.bill_no
     , b.total_amount
     , b.created_at
     , b.id
     , ( SELECT sum(gst_amt) 
        FROM bill_contents_pharmacy bc 
       WHERE bc.bill_id = b.id) as tgst 
  FROM bills b 
 WHERE b.bill_type = 'pharmacy' 
   AND cast(b.cr_at as date) >= '2017-08-01' 
   AND cast(b.cr_at as date) <= '2017-08-23' 
   AND b.is_cancelled = 0 
   AND b.is_deleted = 0 
 ORDER 
  BY b.bill_no ASC

This query is taking around 10 minutes inside my php code, but when I run it in phpmyadmin it's done in just 3.6 seconds. What am I doing wrong?

答

You also can try This and you have to create relationship on bills and bill_contents_pharmacy tables and create index.

SELECT b.bill_no, b.total_amount, b.created_at, b.id, sum(bc.gst_amt) as tgst
FROM bills b
JOIN bill_contents_pharmacy bc ON bc.bill_id = b.id
WHERE b.bill_type = 'pharmacy'
AND cast(b.cr_at as date) >= '2017-08-01'
AND cast(b.cr_at as date) <= '2017-08-23'
AND b.is_cancelled = 0
AND b.is_deleted = 0 
GROUP BY b.bill_no, b.total_amount, b.created_at, b.id
ORDER BY b.bill_no ASC

This must Speed up your query

答

The problem seems to be that subquery. Try using a join instead.

SELECT b.bill_no, b.total_amount,b.created_at, b.id, sum(bc.gst_amt) as tgst 
FROM bills b LEFT JOIN bill_contents_pharmacy bc ON (bc.bill_id = b.id)
WHERE b.bill_type = 'pharmacy' AND cast(b.cr_at as date) >= '2017-08-01' 
AND cast(b.cr_at as date) <= '2017-08-23' 
AND b.is_cancelled IS FALSE AND b.is_deleted IS FALSE 
GROUP BY b.bill_no, b.total_amount, b.created_at, b.id
ORDER BY b.bill_no ASC

Also the casting to date may not be necessary.

Mysql查询需要大约10分钟才能执行

相关推荐