MySQL Forums
Forum List  »  Performance

Re: Help improve query slowness
Posted by: Øystein Grøvlen
Date: August 24, 2018 12:57AM

A bit difficult to say what is the problem without seeing the output of EXPLAIN, but as a general advice: Use IN instead of EXIST wherever possible. MySQL optimizes IN-subqueries more efficiently than EXIST subqueries. In your case, try rewriting your query to something like:

FROM g_prdsub
WHERE (g_prdsub.prodnum, g_prdsub.prod_code) IN
(SELECT d.prodnum, d.prod_code
FROM fullProductsTbl d
JOIN g_prdmst p ON d.prodnum = p.prodnum AND d.curcode = p.curcode
WHERE d.duplicate = 1
AND g_prdsub.prodsub NOT IN (SELECT a.prodsub FROM a_assets a);

(I also converted the inner subquery to a JOIN.)

I would also think that combined indexes on (prodnum, prod_code) and (prodnum, curcode) would be useful.

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway

Options: ReplyQuote

Written By
August 23, 2018 07:51AM
August 23, 2018 10:32AM
Re: Help improve query slowness
August 24, 2018 12:57AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.