How To Reduce Nested Query Time using mysql

Question

How Can i reduce this query run time with any type of join or sub-query or nested query.

select 
`p`.`id`,
`p`.`name`,
SUM(s.quantity) as inst,
from `product` as `p` 
LEFT JOIN  sales as s ON s.pid=p.`id` AND s.sales_id IN (SELECT invoice.invoice_id FROM invoice WHERE invoice.ccid NOT IN (SELECT ccid FROM ticket WHERE st='1'))
where `p`.`hc` = '1' 
GROUP BY `p`.`id` 
order by `p`.`name` DESC

use inner join instead on in query and left join instead of not in .....that will reduce query time because in query take much time — Ankit Agrawal
– Ankit Agrawal, Commented Jun 10, 2016 at 9:15
select p.id, p.name AS name, SUM(ss.instock) as inst from product as p LEFT JOIN (SELECT s.id,s.pid,CASE COUNT(crt.salvage_part) WHEN 1 THEN 0 ELSE s.quantity END AS instock FROM sales as s LEFT JOIN invoice as i ON i.invoice_id=s.sales_id LEFT JOIN crt ON crt.cc_id=i.cc_id AND crt.st='1' GROUP BY s.id) as ss ON ss.pid=p.id where p.hc = '1' GROUP BY p.id order by p.name DESC Tried This Too, But Can't Reduce it's runtime. — Fahad Bhuyian
– Fahad Bhuyian, Commented Jun 10, 2016 at 9:23
The question is nonsensical as, clearly, this query would produce a syntax error. — Strawberry
– Strawberry, Commented Jun 11, 2016 at 14:12

Navnath · Accepted Answer · 2016-06-10 09:52:30Z

1

Add index on st from ticket and also add index on ccid from invoice and sales_id from sales.

Try using Explain clause before your query whether created index is being used or not in possible_keys column output .

answered Jun 10, 2016 at 9:52

Navnath

1331 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Fahad Bhuyian Over a year ago

Please explain what you mean. with example if possible.

Fahad Bhuyian Over a year ago

Not Working using index

Fahad Bhuyian · Accepted Answer · 2016-06-11 14:01:07Z

0

Finally i come to a solution & it works

select 
s.pid,
SUM(s.quantity) AS squan 
from invoice as i 
LEFT JOIN sales as s ON s.sales_id=i.invoice_id
LEFT JOIN crt ON crt.cc_id=i.cc_id AND crt.ch='0'
WHERE 
crt.ch='0' 
AND 
(i.cc_id!='' OR i.cc_id!='0') 
GROUP BY i.invoice_id

answered Jun 11, 2016 at 14:01

Fahad Bhuyian

3552 silver badges10 bronze badges

1 Comment

Strawberry Over a year ago

You SELECT pid, but GROUP BY invoice_id. This is liable to provide an indeterminate/erroneous result.

Collectives™ on Stack Overflow

How To Reduce Nested Query Time using mysql

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related