I am not a CMDBA yet but I've been using mysql for a while. Please give more details! e.g. the full query, the table structure, the view structure, number of rows in the table etc. As you do that, here's some general info, this article<http://forums.mysql.com/read.php?100,191257,225121#msg-225121>says the performance of a query inside a view is not different from executing the query direct; if that be the case, then the culprit is either the query (poor use of indices comes to mind) or the table. You are aware that<http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html>EXISTS and NOT EXISTS return true and false respectively depending on the condition? I once confused those two with IN and NOT IN and pulled out a sizable chunk of my hair trying to find the cause. Regards, Peter. On Fri, May 22, 2009 at 9:15 AM, Oj G <ojiwaj@yahoo.com> wrote:

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

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.