Solved

optimizing a sql syntax

Posted on 2011-03-06
5
258 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
  • 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
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
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

679 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