Solved

optimizing a sql syntax

Posted on 2011-03-06
5
261 Views
Last Modified: 2012-05-11
I have few tables under the database: CRMDB.
The table books is very huge, almost 13712821 rows [aprox].
I feel that the following sysntax would be very costly:
delete from CRMDB.books where custId not in (select custId from CRMDB.customers);

Is there any way to overcome the costly not in command??
0
Comment
Question by:pvinodp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 200 total points
ID: 35053747
Backup database and try these alternative queries
DELETE b.*
FROM    CRMDB.books b
        LEFT JOIN CRMDB.customers c ON b.custId = c.custId
WHERE   c.custId IS NULL

Open in new window


DELETE  CRMDB.books
WHERE   NOT EXISTS ( SELECT *
                     FROM   CRMDB.customers
                     WHERE  CRMDB.books.custId = CRMDB.customers.custId ) ;

Open in new window

0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 300 total points
ID: 35053806
Instead of * you can go with 1 in the sub-query.
DELETE  CRMDB.books
WHERE   NOT EXISTS ( SELECT 1
                     FROM   CRMDB.customers
                     WHERE  CRMDB.books.custId = CRMDB.customers.custId );

Open in new window

0
 

Author Comment

by:pvinodp
ID: 35054112

I tried and getting this error:

delete from omcr.HistoricalAlarmNotification WHERE   NOT EXISTS ( select 1 from omcr.HistoricalAlarmLifecycle where omcr.HistoricalAlarmNotification.halcid omcr.HistoricalAlarmLifecycle.halcid );

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'omcr.HistoricalAlarmLifecycle.halcid )' at line 1
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35054155
you missed =
DELETE FROM omcr.HistoricalAlarmNotification 
      WHERE NOT EXISTS (SELECT 1 
                          FROM omcr.HistoricalAlarmLifecycle 
                         WHERE omcr.HistoricalAlarmNotification.halcid = omcr.HistoricalAlarmLifecycle.halcid);

Open in new window

0
 

Author Closing Comment

by:pvinodp
ID: 35125840
I thank you all for your inputs
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question