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:

SELECT COUNT(*)
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


Subject
Views
Written By
Posted
324
August 23, 2018 07:51AM
186
August 23, 2018 10:32AM
Re: Help improve query slowness
181
August 24, 2018 12:57AM


Sorry, only registered users may post in this forum.

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.