this report of clients who used a service but had zero charge INVOICE has 132000 distinct recs vw_use has 170000 distinct recs vw charge has 140000 distinct recs the views are projections for /country ALL THE UNDERLYING KEY FIELDS ARE INDEXED ON KEYS THE MYSQL QUERY, INSERT INTO REPORT() SELECT custnum,region,status,invoice,inv_date,price,discount FROM INVOICE i WHERE EXISTS(select * from vw_use u where i.custnum=u.custnum) AND NOT EXISTS (select * from vw_charge c where i.invoice=c.invoice) Bernard, Nicholas thanx so far... The optimist sees the doughnut; the pessimist the hole. --- On Fri, 5/22/09, bernard kioko <bernsoft@gmail.com> wrote: From: bernard kioko <bernsoft@gmail.com> Subject: Re: [Skunkworks] myDBA gurus To: "Skunkworks forum" <skunkworks@lists.my.co.ke> Date: Friday, May 22, 2009, 1:05 AM Also,,,,consider pasting the query here for people to assist you even better...... sometimes its easier to pick the problem when you are seeing it visually. Regards On Fri, May 22, 2009 at 11:04 AM, Nicholas Loki <lokimwenga@yahoo.com> wrote: IMO I think the performance of the subquery found in the WHERE EXISTS and WHERE NOT EXISTS clauses could be affecting the performance of your view. See if you can tune this query either by adding indexes to the key fields of your query or using conditions that allow you to fetch smaller datasets if possible. Also consider doing the same for your outer query. "Excellent people exceed expectations". From: Oj G <ojiwaj@yahoo.com> To: Skunkworks forum <skunkworks@lists.my.co.ke> Sent: Friday, May 22, 2009 9:15:22 AM Subject: [Skunkworks] myDBA gurus mySQL 5 is performing poorly when it comes to WHERE EXISTS AND NOT EXIST in views... any myDBA with ideas how to optimize this...? ======================================== The optimist sees the doughnut; the pessimist the hole. _______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks Other services @ http://my.co.ke Other lists ------------- Skunkworks announce: http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks-announce Science - http://lists.my.co.ke/cgi-bin/mailman/listinfo/science kazi - http://lists.my.co.ke/cgi-bin/mailman/admin/kazi/general -----Inline Attachment Follows----- _______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks Other services @ http://my.co.ke Other lists ------------- Skunkworks announce: http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks-announce Science - http://lists.my.co.ke/cgi-bin/mailman/listinfo/science kazi - http://lists.my.co.ke/cgi-bin/mailman/admin/kazi/general